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
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;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"]
Check your understanding
Why group by DATE(order_ts) instead of order_ts?
Truncating precision away is what makes group by day possible.
Show answer
A — Raw timestamps like 09:15 and 18:42 never match, so you must truncate to the day — Truncating precision away is what makes group by day possible.
What does AVG(revenue) OVER (ORDER BY day ROWS BETWEEN 6 PRECEDING AND CURRENT ROW) compute?
The window spans 6 earlier rows plus the current row, which is 7 days of data.
Show answer
A — A 7-day rolling average — The window spans 6 earlier rows plus the current row, which is 7 days of data.
Which is a pitfall the lesson lists?
The lesson also lists partial days from ingestion delays and different week definitions.
Show answer
A — Time zones (UTC versus local) — The lesson also lists partial days from ingestion delays and different week definitions.
What are the three steps of any time-series metric?
Truncate to the bucket, group by it, then smooth if needed.
Show answer
A — Truncate timestamp, group by bucket, optionally smooth with a window function — Truncate to the bucket, group by it, then smooth if needed.
🧪 Try It Yourself
Exercise 1 – Truncate a timestamp to a day
Tab indents. Press Escape, then Tab, to leave the editor. Control plus Enter runs the code; Control plus Shift plus Enter submits it.
Press Run to see what your code prints.Exercise 2 – Daily revenue with GROUP BY
Tab indents. Press Escape, then Tab, to leave the editor. Control plus Enter runs the code; Control plus Shift plus Enter submits it.
Press Run to see what your code prints.Exercise 3 – Filter by a date range
Tab indents. Press Escape, then Tab, to leave the editor. Control plus Enter runs the code; Control plus Shift plus Enter submits it.
Press Run to see what your code prints.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.