CODEWITHSUNDEEP
sqlgoogle-bigquery
KamS
KamS

Reputation: 182

How to group data in the Google Big Query by date (daily, monthly, and yearly)

I prepared the SQL query to select data grouped by date and source. Thanks to that I know, that on a given day I had X customer's requests from the source_a and Y from source_b and Z form source_c.

The query is:

SELECT
  DATE(TIMESTAMP_SECONDS(CAST(CAST(JSON_VALUE(DATA, '$.createdAt') AS INT64) / 1000 AS INT64))) AS date,
  JSON_VALUE(DATA, '$.source') AS source,
  COUNT(*) AS source_request_count
FROM `my_big_query_table`
GROUP BY
  date, source
ORDER BY date DESC

The output is:

date source source_request_count
2024-10-03 source_a 10
2024-10-03 source_b 5
2024-10-03 source_c 7
2024-10-02 source_a 9
2024-10-02 source_c 6

Question:

I'd like to ask, how to rebuild the query to group data by source and month (or year). For instance, to return values in January 2024?

Expected output:

date source source_request_count
Jun-24 source_a 100
Jun-24 source_b 50
Jun-24 source_c 70
Feb-24 source_a 90
Feb-24 source_c 60

Thank you for your help.

Upvotes: 0

Views: 57

Answers (1)

KamS
KamS

Reputation: 182

Ok, the solution is formatting the timestamp using FORMAT_TIMESTAMP function and then just order descending.

The query I have looked for is:

SELECT
  FORMAT_TIMESTAMP('%Y-%m', TIMESTAMP_SECONDS(CAST(CAST(JSON_VALUE(DATA, '$.createdAt') AS INT64) / 1000 AS INT64))) AS month,
  JSON_VALUE(DATA, '$.source') AS source,
  COUNT(*) AS source_request_count
FROM `my_big_query_table`
GROUP BY
  month, source
ORDER BY month DESC;

Upvotes: 1

Related Questions