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

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;

Important pitfalls

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

The pipeline behind every time-series metric

Whether you’re building DAU, weekly revenue, or a 7-day moving average, the pipeline is always the same three steps: truncate a precise timestamp down to the bucket you care about, group by that bucket, then optionally smooth the result with a window function.

diagram Time-bucketing pipeline mermaid

  flowchart LR
  A["order_ts (exact timestamp)"] --> B["DATE(order_ts) -> day bucket"]
  B --> C{"GROUP BY day"}
  C --> D["One row per day"]
  D --> E["AVG(...) OVER (ROWS BETWEEN 6 PRECEDING) -> 7-day rolling avg"]

A precise timestamp is truncated to a bucket (day/week/month), grouped, and optionally smoothed with a window function.

Analyst’s tip

DATE(order_ts) truncates a timestamp down to just the calendar day — that’s what makes “group by day” possible at all, since two orders placed at 09:15 and 18:42 on the same day would otherwise never match on the raw timestamp. The same idea extends to weeks and months; you’re always truncating precision away before you group.

🧪 Try It Yourself

Exercise 1 – Truncate a timestamp to a day

pch.exerciseTag pch.exerciseTitle

Exercise 2 – Daily revenue with GROUP BY

pch.exerciseTag pch.exerciseTitle

Exercise 3 – Filter by a date range

pch.exerciseTag pch.exerciseTitle

Next

SQL from Python (pandas + sqlite3) shows how to run these same queries from a script and load the results straight into a DataFrame for further analysis.