SQL Academy · Lesson

Common Date Reports (MTD, WoW, YoY)

Build month-to-date, week-over-week, and year-over-year reports with date_trunc and self-joins or window functions.

Common Date Reports (MTD, WoW, YoY) is a free SQL Academy lesson on CoddyKit — lesson 4 of 4. You can read the complete lesson below for free — then practise it hands-on in the browser with a built-in code editor and a 24/7 AI tutor. It is part of the SQL Academy learning path, one of 4 lessons in the course, and your progress syncs across the web and the CoddyKit app.

Three Classic Period Reports

Almost every business dashboard needs:

MTD — Month-to-Date
WoW — Week-over-Week
YoY — Year-over-Year

Month-to-Date

Sum of this month so far:

SELECT SUM(total) AS mtd_revenue
FROM orders
WHERE created_at >= date_trunc('month', NOW())
  AND created_at <  date_trunc('month', NOW()) + INTERVAL '1 month';

Year-to-Date

Same pattern, year granularity:

SELECT SUM(total) AS ytd_revenue
FROM orders
WHERE created_at >= date_trunc('year', NOW());

Week-over-Week

Compare this week vs the previous one:

SELECT
  SUM(total) FILTER (
    WHERE created_at >= date_trunc('week', NOW())
  ) AS this_week,
  SUM(total) FILTER (
    WHERE created_at >= date_trunc('week', NOW()) - INTERVAL '1 week'
      AND created_at <  date_trunc('week', NOW())
  ) AS last_week
FROM orders
WHERE created_at >= date_trunc('week', NOW()) - INTERVAL '1 week';

Year-over-Year

Same period vs same period last year:

SELECT date_trunc('month', created_at) AS month, SUM(total)
FROM orders
WHERE created_at >= NOW() - INTERVAL '2 years'
GROUP BY 1
ORDER BY 1;

-- Then plot with two lines: current year, previous year.

Daily Series Without Gaps

If no orders happen on a day, GROUP BY skips it. Generate a full calendar series and LEFT JOIN:

SELECT day, COALESCE(SUM(o.total), 0) AS revenue
FROM generate_series(
  CURRENT_DATE - INTERVAL '30 days',
  CURRENT_DATE,
  INTERVAL '1 day'
) AS day
LEFT JOIN orders o ON date_trunc('day', o.created_at) = day
GROUP BY day
ORDER BY day;

Running Totals

A window function computes a running sum without self-joins:

SELECT day,
       revenue,
       SUM(revenue) OVER (ORDER BY day) AS running_total
FROM daily_revenue
ORDER BY day;

Period-over-Period Delta

Year-over-year growth using LAG:

SELECT month,
       revenue,
       LAG(revenue, 12) OVER (ORDER BY month) AS prev_year,
       (revenue - LAG(revenue, 12) OVER (ORDER BY month))
         / NULLIF(LAG(revenue, 12) OVER (ORDER BY month), 0) AS yoy_growth
FROM monthly_revenue;

Last 7 / 30 Days (Trailing Window)

Trailing window vs calendar-aligned. Pick what your business actually means:

-- Trailing 7 days, ending now:
WHERE created_at >= NOW() - INTERVAL '7 days';

-- This calendar week, from Monday:
WHERE created_at >= date_trunc('week', NOW());

Time Zone Considerations

"Today" depends on the user's zone. Convert before truncating:

SELECT date_trunc('day', created_at AT TIME ZONE 'America/Los_Angeles') AS la_day,
       COUNT(*)
FROM events
GROUP BY 1;

Pre-Aggregating with Materialised Views

If a report runs every page load, materialise it once a day. Covered in Materialized Views.

Recap

Period reports rely on a few patterns.

Half-open intervals with date_trunc
FILTER for parallel period buckets
generate_series + LEFT JOIN for gap-filled days
LAG for period-over-period deltas

Quick Check

Which expression marks "the first moment of this month"?

Frequently asked questions

Is the “Common Date Reports (MTD, WoW, YoY)” lesson free?

Yes — the full text of “Common Date Reports (MTD, WoW, YoY)” is free to read here on the web, and the SQL Academy course includes 4 lessons in total. To practise it interactively (a built-in code editor and a 24/7 AI tutor) and unlock the rest of the SQL Academy course, upgrade to CoddyKit PRO.

What will I learn in “Common Date Reports (MTD, WoW, YoY)”?

Build month-to-date, week-over-week, and year-over-year reports with date_trunc and self-joins or window functions. You practise SQL Academy with hands-on code you run directly in the browser, and a 24/7 AI tutor answers your questions as you work through the lesson.

Do I need any experience to start SQL Academy?

No prior experience is required. SQL Academy on CoddyKit is structured for beginners through advanced learners; this is — lesson 4 of 4, so you can start here or from the beginning and move at your own pace.

How long does the “Common Date Reports (MTD, WoW, YoY)” lesson take?

Most CoddyKit lessons take about 5–10 minutes. Each one is bite-sized and interactive, so you make steady progress and pick up exactly where you left off across the web and the app.

Can I write and run code in this SQL Academy lesson?

Yes. Every SQL Academy lesson includes a built-in code editor, so you write and run real code right in your browser and get instant AI feedback — no local setup required.

All lessons in this course

← Back to SQL Academy