How to query many cases in SQL CASE statement-CodePudding

I am trying to solve an SQL online-challenge

I have three tables:

sales: customer_id, order_date, product_id.
members: join_date, member_id.
menu: product_id, product_name, price.

and one of the questions is: What is the total items and amount spent for each member before they became a member?

I think I got the write answer right the following query:

with cte as
(
  SELECT 
    CASE WHEN s.customer_id = 'A' THEN count(s.product_id)*m.price END AS purchases_A,
    CASE WHEN s.customer_id = 'B' THEN count(s.product_id)*m.price END AS purchases_B,
    CASE WHEN s.customer_id = 'C' THEN count(s.product_id)*m.price END AS purchases_C,
    case when s.customer_id = 'A' THEN count(s.product_id) END AS total_A,
    case when s.customer_id = 'B' THEN count(s.product_id) END AS total_B,
    case when s.customer_id = 'C' THEN count(s.product_id) END AS total_C
  from sales s
  join menu m on s.product_id = m.product_id
  join members mb on mb.customer_id = s.customer_id and mb.join_date > s.order_date
  group by s.customer_id, m.price
)
select 
  sum(purchases_A) as total_spendings_a,
  sum (total_A) as total_items_A,
  sum(purchases_B) as total_spendings_b,
  sum (total_B) as total_items_B,
  sum(purchases_C) as total_spendings_c,
  sum (total_C) as total_items_C
from cte;

And my question is. is there a better way or more efficient way in writing this query? it feels too long and repetitive. In this case I had only three customers: A,B, and C what if I have 100 or 1000 customers?

many thanks in advance

CodePudding user response：

Simply join the tables and aggregate by member:

select
  m.member_id,
  coalesce(count(*), 0) as total_amount,
  coalesce(sum(price), 0) as total_price
from members m
left join sales s on s.customer_id = m.customer_id and s.order_date < m.join_date
left join menu p on p.product_id = s.product_id
group by m.member_id
order by by m.member_id;

You mention both members.member_id and members.customer_id, so I've used them both. If this was a typo in your request, then just change the wrong column name to the correct one in my query.