Telegram API Question posted in
A comprehensive official documentation can be found here.

Joining to the same table with different filters – Telegram API

I am trying to count, for each id, the number of distinct app downloaded by the user, grouping the count by category. An example of the separate queries are as such:

SELECT id
,COUNT(DISTINCT app) AS gaming_apps
FROM apps_table 
WHERE app IN ('Clash of Clans', 'Valorant', 'PUBG') 
GROUP BY id 

SELECT id 
,COUNT(DISTINCT app) AS msg_apps
FROM apps_table 
WHERE app IN ('Telegram', 'WhatsApp', 'Signal', 'FBMessenger') 
GROUP BY id 

SELECT id 
,COUNT(DISTINCT app) AS fin_apps
FROM apps_table 
WHERE app IN ('Yahoo Finance', 'Robinhood') 
GROUP BY id

Ideally, I would want to return a table with the columns id gaming_apps msg_apps fin_apps. I thought about LEFT JOIN the 3 queries together but I am not sure how, and wrapping each as a subquery would be too unwieldy.

I also tried this, to no avail because a new row is inserted per download or app update:

SELECT id
,COUNT(CASE WHEN app IN ('Clash of Clans', 'Valorant', 'PUBG') THEN 1 ELSE NULL END) AS gaming_apps
,COUNT(CASE WHEN app IN ('Telegram', 'WhatsApp', 'Signal', 'FBMessenger')  THEN 1 ELSE NULL END) AS msg_apps
,COUNT(CASE WHEN app IN ('Yahoo Finance', 'Robinhood') THEN 1 ELSE NULL END) AS fin_apps 
FROM apps_table 
GROUP BY id
Tags:

3

Answers


  1. Chosen as BEST ANSWER
    0

    I figured out that adding OVER (PARTITION BY id) also works in counting only distinct records (not sure why?), albeit returning duplicate rows of the same values depending on how many app entries each id has in the table. Without OVER (PARTITION BY id), it will simply count the matches without consideration of distinct values, inflating the number if the id has multiple entries of the same app.

    SELECT id
,SUM(CASE WHEN app IN ('Clash of Clans', 'Valorant', 'PUBG') THEN 1 ELSE 0 END) OVER (PARTITION BY id) AS gaming_apps
,SUM(CASE WHEN app IN ('Telegram', 'WhatsApp', 'Signal', 'FBMessenger')  THEN 1 ELSE 0 END) OVER (PARTITION BY id) AS msg_apps
,SUM(CASE WHEN app IN ('Yahoo Finance', 'Robinhood') THEN 1 ELSE 0 END) OVER (PARTITION BY id) AS fin_apps 
FROM apps_table 
GROUP BY app, id

    To remove the duplicates, I then wrap the results as a subquery and SELECT DISTINCT or using ROW_NUMBER() OVER (PARTITION BY id) to create a ranking to then filter with.


  2. 0

    You are close, you can use SUM instead of count in your query.

    SELECT id
,SUM(CASE WHEN app IN ('Clash of Clans', 'Valorant', 'PUBG') THEN 1 ELSE 0 END) AS gaming_apps
,SUM(CASE WHEN app IN ('Telegram', 'WhatsApp', 'Signal', 'FBMessenger')  THEN 1 ELSE 0 END) AS msg_apps
,SUM(CASE WHEN app IN ('Yahoo Finance', 'Robinhood') THEN 1 ELSE 0 END) AS fin_apps 
FROM apps_table 
GROUP BY id

    I think this should work. if it doesnt work then, like you said do a left join.

    SELECT main.id as id,
gaming_apps,
msg_apps, 
...

FROM apps_table  main
LEFT OUTER JOIN (
SELECT id,COUNT(DISTINCT app) AS gaming_apps FROM apps_table  WHERE app IN ('Clash of Clans', 'Valorant', 'PUBG')  GROUP BY id) gaming_apps ON gaming_apps .id=main.id
LEFT OUTER JOIN (
SELECT id ,COUNT(DISTINCT app) AS msg_apps FROM apps_table  WHERE app IN ('Telegram', 'WhatsApp', 'Signal', 'FBMessenger')  GROUP BY id) msg_apps ON msg_apps.id=main.id 
...
    Login or Signup to reply.
  3. 0

    If you want distinct counts, then use count(distinct):

    SELECT id,
       COUNT(DISTINCT CASE WHEN app IN ('Clash of Clans', 'Valorant', 'PUBG') THEN app END) AS gaming_apps,
       COUNT(DISTINCT CASE WHEN app IN ('Telegram', 'WhatsApp', 'Signal', 'FBMessenger') THEN app END) AS msg_apps,
       COUNT(DISTINCT CASE WHEN app IN ('Yahoo Finance', 'Robinhood') THEN app END) AS fin_apps 
FROM apps_table 
GROUP BY id ;

    Note that ELSE NULL is redundant, because NULL is the default value if there are no matches in the CASE expression.

    Login or Signup to reply.
Please signup or login to give your own answer.

Back To Top
Search