I’ve been trying to build a pie chart in a metabase question, to put into my dashboard. I have player_id and updated_at columns, and I executed a consult to return the number of updated_at by day. I done this with a problem, I can’t use order by, I don’t know why but this isn’t working. So, I would like to know where I am erring.
SQL:
x
SELECT * FROM (
SELECT date(updated_at, '%Y-%m-%d') AS d, count(DISTINCT player_id) AS c FROM matchmaking_stats_summary WHERE updated_at between '2020-02-01 00:00:00' and '2020-02-01 23:59:59' union
SELECT date(updated_at, '%Y-%m-%d') AS d, count(DISTINCT player_id) AS c FROM matchmaking_stats_summary WHERE updated_at between '2020-02-02 00:00:00' and '2020-02-02 23:59:59' union
SELECT date(updated_at, '%Y-%m-%d') AS d, count(DISTINCT player_id) AS c FROM matchmaking_stats_summary WHERE updated_at between '2020-02-03 00:00:00' and '2020-02-03 23:59:59' union
SELECT date(updated_at, '%Y-%m-%d') AS d, count(DISTINCT player_id) AS c FROM matchmaking_stats_summary WHERE updated_at between '2020-02-04 00:00:00' and '2020-02-04 23:59:59' union
SELECT date(updated_at, '%Y-%m-%d') AS d, count(DISTINCT player_id) AS c FROM matchmaking_stats_summary WHERE updated_at between '2020-02-05 00:00:00' and '2020-02-05 23:59:59'
) AS x
ORDER BY d asc
The column updated_at type is timestamp. A value example is: 2020-02-01 00:47:32 And another question is why I can’t set the Dimession and Meassure in Metabase, I would like to set Dimession label to respective date interval and the count number in Meassure label. Is it possible?
Advertisement
Answer
Why not use group by
?
SELECT date(updated_at, '%Y-%m-%d') AS d,
count(DISTINCT player_id) AS c
FROM matchmaking_stats_summary
WHERE updated_at >= '2020-02-01 00:00:00' and
updated_at < '2020-02-06 00:00:00'
GROUP BY date(updated_at, '%Y-%m-%d') ;