I don't know exactly which SQL language you use and I do see some inconsistenies in your code (sometimes JOIN ON, sometimes JOIN [table] ON;), but I hope I can help a bit.
You can always check out the 'computing cost' by putting EXPLAIN at the start of your code in the SQL Runner, or for some real advanced analysis of your code, there is this online tool that you can use: http://tatiyants.com/pev/#/plans (not sure if it is only for PostgreSQL though).
I believe that, in your code, the "SELECT COUNT(...) FROM ... WHERE ..."-statements are performed for EACH row, every time again.
It might help to first do the counting on the separating id's and JOIN those aggregated tables to the desired table.
Your code would then look something like this:
row_number() OVER(ORDER BY a.customer_id) AS prim_key,
tt.name AS "error_message_category",
bn.count AS "box_number",
tn.count AS "Attempt_number",
spo.sum AS "total_net_invoiced_sp"
ro.sum As "total_net_invoiced_no_sp"
FROM staging.memberships_autoship_box b
JOIN staging.memberships_autoship a ON a.id = b.autoship_id
JOIN staging.memberships_autoship_box_transaction t ON b.autoship_box_id = t.autoship_box_id
JOIN staging.memberships_autoship_box_transaction_type tt ON tt.autoship_box_transaction_type_id = t.autoship_box_transaction_type_id AND tt.level = 'failure'
JOIN camel.d_address da ON da.id = a.id
JOIN camel.d_member d ON d.id = a.id
/** Below are the ‘new’ JOIN’s, where all calculations are done once and for all; **/
JOIN (SELECT autoship_id, COUNT(autoship_box_id) count FROM staging.memberships_autoship_box GROUP BY autoship_id) bn ON a.id = bn.autoship_id
JOIN (SELECT autoship_box_id, COUNT(autoship_box_transaction_id) count FROM staging.memberships_autoship_box_transaction GROUP BY autoship_box_id) tn ON b.autoship_box_id = tn.autoship_box_id
JOIN (SELECT id, SUM(cad_net_invoiced) sum FROM camel.f_item_sold GROUP BY id) spo ON d.id = spo.id
JOIN (SELECT id, SUM(cad_net_invoiced) sum FROM camel.f_item_sold WHERE order_type_id != 12 GROUP BY id) ro ON d.id= ro.id ;;
Please let me know if this worked for you, for I can not be 100% sure. (And maybe someone else finds a much better and efficient solution. )