0

I have encountered a question with the following table:

Column Name Type
user_id int
spend_date date
platform enum
amount int

The goal is to find the total number of users and the total amount spent using mobile platform only, desktop platform only and both mobile and desktop together for each date. For example, if on 2017-07-01 user '1' used mobile and desktop to make a purchase, this purchase would be accounted for "both mobile and desktop together". If user '2' used mobile to make a purchase but never used desktop, this purchase would show up in "mobile only."

My code looks like the following. There is no purchase by anyone using both mobile and desktop on 2017-07-02, thus there should be an extra row ['2017-07-02', 0,0]. However, this row does not show up. Can anyone please let me know what's wrong with my code:

with 
mobile_users as (select distinct user_id 
                 from spending 
                 where platform = 'mobile'),
desktop_users as (select distinct user_id 
                  from spending 
                  where platform = 'desktop')
(select spend_date, platform, count(user_id) as total_users, sum(amount) as total_amount 
 from spending
 where user_id not in (select user_id 
                       from mobile_users)
 group by spend_date)
union all
(select spend_date, platform, count(user_id) as total_users, sum(amount) as total_amount 
 from spending
 where user_id not in (select user_id 
                       from desktop_users)
 group by spend_date)
union all
(select spend_date, 'both' as platform, count(user_id) as total_users, sum(amount) as total_amount 
 from spending
 where user_id in (select user_id from 
                   desktop_users) 
   and user_id in (select user_id 
                   from mobile_users)
 group by spend_date)
order by spend_date
3
  • 1
    You will need a Calendar table. If you are on 8 you can use a recursive CTE instead Dec 29, 2020 at 8:57
  • "There is no purchase by anyone using both mobile and desktop on 2017-07-02, thus there should be an extra row ['2017-07-02', 0,0]." – Your output shows the results by platform. So if a date is missing, would you like it to be returned with zeros for each platform as well as for both, i.e. three times?
    – Andriy M
    Dec 29, 2020 at 11:28
  • what will be the value in platform column if there is no purchase ? Dec 30, 2020 at 7:28

1 Answer 1

0

The thing is you are doing all pruning on Mobile or Desktop or both, you haven't add any condition for other than this values.

What I have done is other than this 3 Mobile or Desktop or both values in table, it will count the row & calculate amount if present.

with 
mobile_users as (select distinct user_id 
                 from spending 
                 where platform = 'mobile'),
desktop_users as (select distinct user_id 
                  from spending 
                  where platform = 'desktop')
(select spend_date, platform, count(user_id) as total_users, sum(amount) as total_amount 
 from spending
 where user_id not in (select user_id 
                       from mobile_users)
 group by spend_date)
union all
(select spend_date, platform, count(user_id) as total_users, sum(amount) as total_amount 
 from spending
 where user_id not in (select user_id 
                       from desktop_users)
 group by spend_date)
union all
(select spend_date, 'both' as platform, count(user_id) as total_users, sum(amount) as total_amount 
 from spending
 where user_id in (select user_id from 
                   desktop_users) 
   and user_id in (select user_id 
                   from mobile_users)
 group by spend_date)
 union all 
(
  /* here the code begins */
select spend_date,  platform, count(user_id) as total_users, sum(amount) as total_amount 
 from spending
 where user_id not in (select user_id from 
                   desktop_users) 
   and user_id not in (select user_id 
                   from mobile_users)
 group by spend_date
)
order by spend_date

Your Answer

By clicking “Post Your Answer”, you agree to our terms of service and acknowledge you have read our privacy policy.

Not the answer you're looking for? Browse other questions tagged or ask your own question.