SQL Solution for Counting Paid and Unpaid Orders per User‑Product in PostgreSQL

Requirement: From order data, count the number of paid and unpaid orders, but treat multiple orders of the same user and product as a single order based on final payment status.

Thought process: Consider grouping by user (una) and product (pid) to deduplicate, but need to output fields (ods, sid) that are not part of the GROUP BY, requiring aggregation functions.

Solution: Use a sub‑query that aggregates order statuses into an array and averages the sid, then apply a CASE expression in the outer query to count successful payments. The full PostgreSQL query is:

select 
    sid::integer,
    sum(case when ods && ARRAY[4,12] then 1 else 0 end) as paid_success_count,
    count(1) as total_count
from (
    select avg(sid) as sid,
           array_agg(ods::int) as ods,
           pid, una, count(1) as topusp
    from bod
    where (date(create_time) >= '2012-9-1' and date(create_time) < '2012-10-1')
      and una not in ('zyqunar','CRYtoDEATH','gambol1','xiaoyaowxl',
                      'zyqunar','watchmange','christy1104','pekhello',
                      'woody88','gambol3','zhaoyun88','maishurong','jnlix')
      and sid != 1
      and sid not in (select id from bsi where sna like '%团购%')
    group by una, pid
) a
group by sid;

Explanation: The inner query groups orders by user and product, aggregates all order status values into an array (ods) and computes the average sid; this removes duplicate orders. After grouping by pid, the average sid still corresponds to a specific product. The outer query uses a CASE WHEN clause to count how many orders have statuses in the target list, yielding the desired counts.