Occurrences by Year-Month in Presto DB

Question

I have a table in Presto with this schema:

created_at  Record
timestamp   String

created_at has records from 2020 to 2022.

What's the best way to get the total number of record by month, like this output:

Date        N_Records
2020-01       1000
2020-02       1500
----
2022-03       3000

What I did so far:

select date_format(created_at, '%b') month, count(*) count
from table
group by date_format(created_at, '%b')
order by 1 asc

Problems with my code:

I don't have the respective year and the results are not sorted by asc month.

Can someone help with to improve my query?

Answer 1

You can use format including 4-digit year and 2-digit month:

select date_format(created_at, '%Y-%m') Date, count(*) N_Records
from table
group by 1
order by 1 asc