Computing cross-category sales from user transaction data - For users who have done more than 1 transaction

Question

Hi,

Input Data

I'm having a daily user level transaction_data and I want to compute monthly cross-category sales - aggregation for users who have bought from more than 1 category only.

Output -

Can someone please suggest how to go about this in sql?

Answer 1

Hmmm . . . this seems to do what you describe:

select date_trunc('month', day), category,
       sum(transaction_amount), count(distinct user_id)
from (select d.*,
             min(category) over (partition by userid, date_trunc('month', day)) as min_category,
             max(category) over (partition by userid, date_trunc('month', day)) as max_category
      from daily d
     ) d
where min_category <> max_category -- at least two categories
group by date_trunc('month', day), category;