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

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

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)

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;

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.

  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"]

🧪 Try It Yourself

Exercise 1 – Truncate a timestamp to a day

Exercise 2 – Daily revenue with GROUP BY

Exercise 3 – Filter by a date range

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.