Daily / Weekly / Monthly Aggregation

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

Salin
-- 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

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

Salin
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

Salin
-- 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

Salin
-- 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;