Trying find a better writing for my SQL Query [closed]

I Have a database of published website pages, with following coulmns:

date| publisher_id | platform | page_views

The platform is the platform the page was showed on (Mobile OR Desktop)

I need to write an SQL query that will meet the following requirement:

For every publisher that has mobile traffic (publisher can publish only at desktop, only at mobile or both), find that publisher’s total amount of page views across all platforms.

I tried the next steps:

1. Create a new column – case when "platform"='Mobile' then 1 else 0 end user_has_mobile_traffic
2. Make the table as Sub-Query, and then group the rows by publisher_id and indictor if he has mobile_traffic
3. Then I got a list of all the publishers that have mobile traffic
4. At the original table, If the publisher_id IN the list i’ve made then sum page_views

What do you think? Is there any easier way, or my way is good enough?

Thanks!

Answer

You can do it without the IN (<sub-query>), reducing the number of times you scan the table, just by using a HAVING clause.

SELECT
  publisher_id,
  SUM(page_views)
FROM
  your_table
GROUP BY
  publisher_id
HAVING
  MAX(CASE WHEN platform = 'Mobile' THEN 1 ELSE 0 END) = 1