Date and Time Analytics in SQL

Why time queries matter

Most analytics dashboards are time-based:

Daily Active Users (DAU)
Weekly revenue
Monthly churn

Dates in SQL (patterns)

Different databases have slightly different date functions.

We’ll use common patterns:

DATE(timestamp)DATE(timestamp) to extract date
Group by date/week/month

Daily metrics

Daily orders

SELECT
  DATE(order_ts) AS day,
  COUNT(*) AS orders,
  SUM(amount) AS revenue
FROM orders
GROUP BY DATE(order_ts)
ORDER BY day;

Daily orders

SELECT
  DATE(order_ts) AS day,
  COUNT(*) AS orders,
  SUM(amount) AS revenue
FROM orders
GROUP BY DATE(order_ts)
ORDER BY day;

Rolling 7-day revenue (window)

7-day rolling revenue

SELECT
  day,
  revenue,
  AVG(revenue) OVER (ORDER BY day ROWS BETWEEN 6 PRECEDING AND CURRENT ROW) AS rev_ma7
FROM (
  SELECT DATE(order_ts) AS day, SUM(amount) AS revenue
  FROM orders
  GROUP BY DATE(order_ts)
) t
ORDER BY day;

7-day rolling revenue

SELECT
  day,
  revenue,
  AVG(revenue) OVER (ORDER BY day ROWS BETWEEN 6 PRECEDING AND CURRENT ROW) AS rev_ma7
FROM (
  SELECT DATE(order_ts) AS day, SUM(amount) AS revenue
  FROM orders
  GROUP BY DATE(order_ts)
) t
ORDER BY day;

Important pitfalls

Time zones (UTC vs local)
Partial days (data ingestion delays)
Week definition (Mon-Sun vs Sun-Sat)

If this helped you, consider buying me a coffee ☕

Buy me a coffee