Q26📈Time Series & Analisis Tanggal★☆☆☆☆Copy-pasteSafe to Run

Daily / Weekly / Monthly Aggregation

Dialect:BigQueryPostgreSQLSnowflakeMySQL

#aggregation#time-series#date-trunc#granularity

Kapan Pakai Query Ini

Foundational query untuk semua analisis time-series. Kamu pakai ini setiap kali mau lihat tren metrik berdasarkan waktu, entah itu harian, mingguan, atau bulanan. Kalau dashboard kamu butuh toggle granularity (daily vs monthly), ini pola dasarnya. Cukup ganti satu parameter di DATE_TRUNC untuk pindah granularity.

Yang Perlu Diganti

your_schema.orders → nama tabel kamu
order_date → kolom tanggal transaksi
revenue → kolom metrik yang mau diagregasi
DAY / WEEK / MONTH → granularity yang kamu mau (pilih salah satu)

Query

-- Ganti DAY, WEEK, atau MONTH sesuai kebutuhan
SELECT
  DATE_TRUNC(order_date, DAY)   AS period,   -- harian
  -- DATE_TRUNC(order_date, WEEK)  AS period, -- mingguan (mulai Minggu)
  -- DATE_TRUNC(order_date, MONTH) AS period, -- bulanan
  COUNT(*)                       AS total_orders,
  SUM(revenue)                   AS total_revenue,
  AVG(revenue)                   AS avg_order_value
FROM your_schema.orders
WHERE order_date BETWEEN '2024-01-01' AND '2024-03-31'
GROUP BY 1
ORDER BY 1;

Contoh Output

period	total_orders	total_revenue	avg_order_value
2024-01-01	143	18,450,000	128,951
2024-01-08	167	22,130,000	132,515
2024-01-15	158	20,780,000	131,519
2024-01-22	189	25,340,000	134,074

Variasi

Tambah PARTITION BY region di window function kalau mau aggregasi per wilayah
Ganti SUM(revenue) dengan COUNT(DISTINCT user_id) untuk DAU/WAU/MAU
Tambah kolom COUNT(DISTINCT user_id) / COUNT(*) AS repeat_rate untuk repeat order rate
Filter WHERE EXTRACT(YEAR FROM order_date) = 2024 untuk batasi ke tahun tertentu
Gunakan QUARTER sebagai granularity untuk agregasi kuartalan

Variasi Dialect

PostgreSQL

SELECT
  DATE_TRUNC('day', order_date)   AS period,   -- harian
  -- DATE_TRUNC('week', order_date)  AS period, -- mingguan
  -- DATE_TRUNC('month', order_date) AS period, -- bulanan
  COUNT(*)                         AS total_orders,
  SUM(revenue)                     AS total_revenue,
  AVG(revenue)                     AS avg_order_value
FROM your_schema.orders
WHERE order_date BETWEEN '2024-01-01' AND '2024-03-31'
GROUP BY 1
ORDER BY 1;

Snowflake

-- Sama dengan PostgreSQL — pakai string quoted untuk unit
SELECT
  DATE_TRUNC('DAY', order_date)   AS period,
  COUNT(*)                         AS total_orders,
  SUM(revenue)                     AS total_revenue,
  AVG(revenue)                     AS avg_order_value
FROM your_schema.orders
WHERE order_date BETWEEN '2024-01-01' AND '2024-03-31'
GROUP BY 1
ORDER BY 1;

MySQL

-- MySQL tidak punya DATE_TRUNC — pakai DATE_FORMAT sebagai pengganti
SELECT
  DATE_FORMAT(order_date, '%Y-%m-%d') AS period,   -- harian
  -- DATE_FORMAT(order_date, '%Y-%u')   AS period, -- mingguan (ISO week)
  -- DATE_FORMAT(order_date, '%Y-%m-01') AS period, -- bulanan
  COUNT(*)                             AS total_orders,
  SUM(revenue)                         AS total_revenue,
  AVG(revenue)                         AS avg_order_value
FROM your_schema.orders
WHERE order_date BETWEEN '2024-01-01' AND '2024-03-31'
GROUP BY 1
ORDER BY 1;