How to fix this order by sql consult? How to change dimension label from pie chart metabase?

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:

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?

Thank you, have a nice day.

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') ;

Advertisement

Answer