Skip to content
Advertisement

Format pivot data with multiple conditions

My current query is

SELECT COUNT (DISTINCT("json_extract_scalar"("data", '$.user_id'))) AS users, event, date(timestamp)
FROM tableName
WHERE category='category'
GROUP BY event, date(timestamp)
ORDER BY date(timestamp) DESC

Right now, the output it displays is of the form:

users | date | event |
3 | 2019-09-25 | visit |
4 | 2019-09-25 | click_button |
10 | 2019-09-25 | visit |

I want it to be of the form

| date | users_visits | users_clicks |
|2019-09-25 | 3 | 4 |
|2019-09-26 | 10 | 0 |

I wrote this query but it does not work, I don’t understand really how to get the counting query inside the pivoting query properly

SELECT date(timestamp), [visit] AS users_visits, [click_button] AS users_clicks
FROM (
  SELECT 
    ("json_extract_scalar"("data", '$.user_id')), event, date(timestamp)
    FROM tableName
    WHERE category='category'
    GROUP BY event, date(timestamp) 
  )
  ) P
PIVOT 
(
  COUNT (DISTINCT  ("json_extract_scalar"("data", '$.user_id')))
  FOR event in 
  ([visit], [click_button])
  ) AS pvt
 order by pvt.date(timestamp)

Advertisement

Answer

Does conditional aggregation work?

SELECT date(timestamp),
       COUNT(DISTINCT CASE WHEN event = 'visit'
                           THEN "json_extract_scalar"("data", '$.user_id')
                      END) as visits,
       COUNT(DISTINCT CASE WHEN event = 'click_button'
                           THEN "json_extract_scalar"("data", '$.user_id')
                      END) as click_button
FROM tableName
WHERE category = 'category'
GROUP BY date(timestamp)
ORDER BY date(timestamp) DESC
7 People found this is helpful
Advertisement