How to calculate day-of-week analysis in SQL

Why day-of-week matters

Your growth PM at DoorDash pings you on a Thursday afternoon: "Engagement looks lumpy this week, can you tell me whether Tuesdays are really softer or whether I am chasing my tail?" You have one query to bring to standup on Friday. The defensible answer is a day-of-week breakdown — counting events, users, orders, and revenue across the seven weekdays and showing where each one sits relative to the average. If Tuesday is a steady twelve percent below the weekly mean every week for a quarter, that is a pattern to build around. If it bounces from minus twenty to plus ten, it is noise.

This pattern shows up everywhere consumer behavior touches calendar rhythm. B2B usage on Notion and Linear is high on weekdays and falls off a cliff on weekends. Consumer commerce on Amazon and Stripe spikes on Sunday evenings and again on Friday paydays. Push notification opens on Uber peak around lunch and dinner. Promo planning, support staffing, ad bid pacing, and outage post-mortems all start with a day-of-week cut. Interviewers at Meta, Snowflake, Airbnb, and Databricks ask candidates to write this in five minutes and to name one trap — usually time zones or the convention of which day is zero.

Postgres, Snowflake, BigQuery, and Redshift all ship an EXTRACT(DOW FROM ...) equivalent, and the same query pattern extends from raw event counts to revenue, retention, and user segmentation. This post walks the query end to end, builds the weekday-versus-weekend split, layers the hourly heatmap on top, normalizes the result so unequal coverage does not lie to you, and collects the traps that get candidates dinged in a forty-five-minute screen.

The EXTRACT building block

Every dialect has a function that returns the day of the week as an integer. In Postgres it is EXTRACT(DOW FROM event_time) and the convention is zero equals Sunday, six equals Saturday. MySQL DAYOFWEEK(event_time) returns one through seven with Sunday equal to one. BigQuery EXTRACT(DAYOFWEEK FROM event_time) matches MySQL. ClickHouse toDayOfWeek(event_time) returns one for Monday through seven for Sunday, which is the ISO 8601 convention.

SELECT EXTRACT(DOW FROM TIMESTAMP '2026-05-13 09:00:00') AS pg_dow;
-- Postgres returns 3, which means Wednesday in the Sunday-zero convention.

A safer pattern is to wrap EXTRACT in a CASE that turns the integer into a string the reader can read at a glance. That removes the off-by-one risk from the output entirely and makes the result self-explanatory in any dashboard.

Baseline DOW query

Count events and distinct users per day of week, summed over a fixed window. Twelve weeks is the sweet spot — long enough to wash out a single bad week and short enough that the product has not drifted underneath you. Always include the named day so the reviewer does not have to remember whether your engine puts Sunday at zero or at one.

SELECT
    EXTRACT(DOW FROM event_time) AS dow,
    CASE EXTRACT(DOW FROM event_time)
        WHEN 0 THEN 'Sunday'
        WHEN 1 THEN 'Monday'
        WHEN 2 THEN 'Tuesday'
        WHEN 3 THEN 'Wednesday'
        WHEN 4 THEN 'Thursday'
        WHEN 5 THEN 'Friday'
        WHEN 6 THEN 'Saturday'
    END AS day_name,
    COUNT(*) AS events,
    COUNT(DISTINCT user_id) AS users
FROM events
WHERE event_time >= CURRENT_DATE - INTERVAL '12 weeks'
GROUP BY 1, 2
ORDER BY 1;

If Monday and Tuesday show the highest event counts and Saturday and Sunday show the lowest, you have a classic B2B SaaS pattern — Notion, Figma, Linear, Vercel all look like this. If Sunday and Friday lead and the rest of the week sits flat, you have consumer commerce. If the curve is nearly flat, you have an always-on utility and the day-of-week framing is not the right lens. Events per day treats one power user with a hundred clicks the same as one hundred users with one click each — distinct users is the cleaner engagement signal, and the two columns moving together is what real engagement looks like.

Weekday vs weekend

The two-bucket split is the lowest-resolution view and the most common ask from a non-technical PM. The trap is that the bucket sizes are unequal — five weekdays and two weekend days — so a raw count comparison will tell you weekdays have more activity even when the per-day activity is identical. Always divide by the number of unique calendar days in each bucket before reporting.

SELECT
    CASE WHEN EXTRACT(DOW FROM event_time) IN (0, 6)
         THEN 'weekend' ELSE 'weekday' END AS day_type,
    COUNT(*) AS total_events,
    COUNT(DISTINCT DATE(event_time)) AS days_covered,
    COUNT(*) * 1.0 / NULLIF(COUNT(DISTINCT DATE(event_time)), 0) AS avg_events_per_day
FROM events
WHERE event_time >= CURRENT_DATE - INTERVAL '8 weeks'
GROUP BY 1;

Read avg_events_per_day, not the raw total. If it is twenty thousand on weekdays and eight thousand on weekends, the product is roughly two and a half times more active on workdays and you should staff support and ship releases accordingly. If the two numbers are within ten percent, weekday-versus-weekend is not informative and you should look at hour-of-day instead.

Hour by DOW heatmap

The two-dimensional cut is where the real story usually lives. A heatmap of hour by day of week shows the peak times for engagement and the dead zones — Sunday evening at nine PM, Friday at four PM, and Tuesday at lunch are three very different patterns and each one drives a different decision.

SELECT
    EXTRACT(DOW FROM event_time) AS dow,
    EXTRACT(HOUR FROM event_time) AS hour,
    COUNT(*) AS events,
    COUNT(DISTINCT user_id) AS users
FROM events
WHERE event_time >= CURRENT_DATE - INTERVAL '4 weeks'
GROUP BY 1, 2
ORDER BY 1, 2;

The output is one hundred sixty-eight rows and plots as a seven-by-twenty-four grid. On a consumer app the heatmap typically shows two diagonal bands: an evening band from Sunday seven PM to Friday eleven PM, and a weekend daytime band on Saturday and Sunday from noon to six PM. On a B2B tool the heatmap shows a tight rectangle from Monday to Friday, nine AM to six PM local time, with almost nothing outside it. Four weeks gives roughly four observations per cell — the minimum to see the shape — and twelve weeks gives you a nearly noise-free heatmap.

Normalized DOW index

Raw counts are hard to compare across products and across time. Normalize each day's count by the weekly average and report a unitless index — one means average, one point two means twenty percent above, zero point eight means twenty percent below. This is the form a PM can drop into a single bar chart.

WITH daily AS (
    SELECT
        DATE(event_time) AS event_date,
        EXTRACT(DOW FROM event_time) AS dow,
        COUNT(*) AS events
    FROM events
    WHERE event_time >= CURRENT_DATE - INTERVAL '12 weeks'
    GROUP BY 1, 2
),
by_dow AS (
    SELECT dow, AVG(events) AS avg_events
    FROM daily GROUP BY 1
),
overall AS (
    SELECT AVG(avg_events) AS grand_mean FROM by_dow
)
SELECT
    b.dow,
    b.avg_events,
    b.avg_events / o.grand_mean AS dow_index
FROM by_dow b CROSS JOIN overall o
ORDER BY b.dow;

The two-step aggregation — daily counts first, then average per day of week — keeps one outsized Tuesday during a launch from dominating the average. Each Tuesday contributes equally and the index reflects a typical Tuesday, not a heroic one. The grand mean is the average of the seven per-DOW averages, not of all daily counts, which would weight by the number of each weekday in the window and mislead you whenever the window is not a clean multiple of seven days.

Common pitfalls

The first trap is the day-numbering convention. Postgres puts Sunday at zero, MySQL and BigQuery put Sunday at one, ClickHouse and the ISO 8601 standard put Monday at one. A query that ports cleanly across engines but reports the wrong weekend is one of the easiest bugs to ship and one of the hardest to spot in review. The fix is to always wrap the integer in a CASE that maps it to a named day before it leaves the query.

The second trap is time zones. Event timestamps are usually stored in UTC, but day of week is a local-calendar concept — a Tuesday in Tokyo is a Monday in California. Running EXTRACT(DOW) on a UTC timestamp gives you the UTC day of week, which is wrong for any user whose local time crosses midnight UTC. Convert to the user's time zone before extracting: EXTRACT(DOW FROM event_time AT TIME ZONE user_tz).

The third trap is uneven coverage at the window edges. If you run twelve weeks back from today and today is a Wednesday, you have twelve Mondays through Wednesdays but only eleven Thursdays through Sundays. Averages computed across these days are weighted unequally. Floor the window to a whole number of weeks with DATE_TRUNC('week', ...) on both bounds.

The fourth trap is holidays masquerading as weekdays. A Monday that falls on a federal holiday at Stripe or Notion looks more like a Sunday than a Monday. Aggregating across twelve weeks washes one holiday out, but a December full of holidays will bias the pattern. Flag holidays with a calendar table and either exclude them from the baseline or report them as a separate category.

The fifth trap is acquisition skew. New users who signed up on weekdays — typical for B2B products with ad spend concentrated on LinkedIn weekday hours — only have weekday history in their first week. Per-user weekend share over a short tenure window will look fake-weekday-heavy as an artifact of when the cohort joined. Restrict the segmentation to users with at least two weeks of history.

Optimization tips

On daily metric tables under a million rows the queries above run in milliseconds. The cost climbs on raw event tables with hundreds of millions of rows. Pre-aggregate to a daily metric table — one row per metric per day per relevant cut — and run the day-of-week queries against that; the shift typically cuts query time by two orders of magnitude.

On Snowflake, BigQuery, and Databricks the event or daily metric table should be clustered or partitioned on the date column. Day-of-week queries always filter on a date range, and clustered storage prunes scanned bytes dramatically. On Postgres the equivalent is a B-tree index on the timestamp column plus declarative range partitioning for large tables.

The day-of-week pattern moves slowly — yesterday's twelve-week DOW breakdown is almost identical to today's. A small materialized view refreshed once a day is enough for any dashboard that needs DOW as input, and it eliminates the heaviest query from the live load. If every query has to call AT TIME ZONE on a billion rows, store a denormalized local_event_date column computed once at ingest and the aggregation becomes a plain group-by with no time-zone arithmetic.

Related reading

• SQL window functions interview questions
• How to calculate autocorrelation in SQL
• How to calculate active days in SQL
• How to detect anomalies in SQL
• A/B testing peeking mistake

If you want to drill SQL questions like this every day, NAILDD is launching with 500+ SQL problems across exactly this pattern.

FAQ

What integer does each engine return for Sunday?

Postgres returns zero for Sunday and six for Saturday. MySQL returns one for Sunday with DAYOFWEEK. BigQuery matches MySQL — one for Sunday — with EXTRACT(DAYOFWEEK FROM ...). ClickHouse uses ISO 8601 where Monday is one and Sunday is seven. Postgres also exposes EXTRACT(ISODOW FROM ...) which matches ClickHouse and is the safer choice when portability matters. The named-day CASE pattern in the body sidesteps the issue entirely by translating the integer into a string before it leaves the query.

How do I define the weekend?

Saturday and Sunday is the default in most western markets. In Israel the weekend is Friday and Saturday, and in many Middle Eastern countries the work week historically ran Sunday through Thursday with Friday and Saturday off. If your product has meaningful traffic outside the US and EU, segment the weekend definition by the user's country before applying it. A single global definition will under-count weekend behavior in those markets and overstate weekday engagement.

How stable is the day-of-week pattern over time?

Stable enough to plan around for most products at the quarter level, drifting at the year level. Twelve weeks is the right window for a tactical decision such as promo timing or on-call staffing. Product launches, viral moments, and macroeconomic shifts can all bend the DOW curve, and a stale baseline silently makes every downstream forecast wrong by the same amount.

When should I use day of month instead of day of week?

Day of week captures human behavioral rhythm — work, leisure, payday, weekend errands. Day of month captures billing cycles — rent, salary deposits, subscription renewals. If you are analyzing engagement on a social app, DOW is the right cut. For SaaS billing, day of month tells you whether failures cluster around the first or the fifteenth.

How long a window should I use?

For a stable DOW baseline twelve weeks is the sweet spot — long enough to wash out any single bad week and short enough that the product has not drifted underneath the analysis. For a quick weekday-versus-weekend check four to eight weeks is enough. For the hour-by-DOW heatmap four weeks gives you roughly four observations per cell, which is the minimum to see the shape; twelve weeks gives you a near-noise-free heatmap at the cost of a slower query.