SQL for Data Analysis Questions

Compute each contract's constant daily rate (total_amount divided by the inclusive day count of its term), generate a month calendar spanning the reporting window, then for every contract-month pair compute the overlap in days between the contract's term and that calendar month. Recognized revenue for that contract in that month is daily_rate * overlap_days; summing across contracts per month gives the portfolio total, and the overlap calculation is exactly what prorates a contract whose start or end falls mid-month.

Approach

1. term_days per contract: inclusive day count from start_date to end_date (julianday(end) - julianday(start) + 1; add 1 because both endpoints belong to the term).
2. daily_rate = total_amount / term_days. This is the core straight-line assumption: revenue accrues evenly, one day at a time, for the life of the contract, not in even monthly chunks.
3. Generate month buckets across the reporting window (a recursive CTE here; a reusable calendar table in production).
4. Join contracts to months wherever the contract's term and the month's range overlap at all: start_date <= month_end AND end_date >= month_start.
5. overlap_days is the inclusive day count of the intersection of [contract start, contract end] and [month start, month end], using MAX() of the two starts and MIN() of the two ends (SQLite's scalar multi-argument MAX()/MIN(), confirmed here to propagate NULL: SELECT MAX(1, NULL, 3) returns NULL). BigQuery's GREATEST()/LEAST() match that NULL-propagating behavior, but Postgres's GREATEST()/LEAST() do not: per the Postgres documentation, they explicitly ignore NULL arguments and only return NULL if every argument is NULL, a documented deviation from the SQL standard. That is not a hypothetical edge case for this query: an open-ended contract with a NULL end_date would make MIN(end_date, month_end) collapse to NULL here (and on BigQuery), silently dropping that contract's row from every month, while the same pattern ported to Postgres's LEAST() would ignore the NULL and keep computing a result, a different, wrong-either-way outcome depending on engine. Guard with COALESCE(end_date, '9999-12-31') (or another far-future sentinel) before the MIN/LEAST call wherever this pattern needs to run identically across engines.
6. recognized_amount = daily_rate * overlap_days, summed per month for the portfolio total.

Worked example

CREATE TABLE contracts (
  contract_id TEXT PRIMARY KEY,
  start_date  TEXT,
  end_date    TEXT,
  total_amount NUMERIC
);

-- C1: mid-month start AND mid-month end (59-day term)
INSERT INTO contracts VALUES ('C1', '2026-01-15', '2026-03-14', 5900);
-- C2: clean calendar-month term (28-day term)
INSERT INTO contracts VALUES ('C2', '2026-02-01', '2026-02-28', 2800);
-- C3: mid-month to mid-month, spans into the next month
INSERT INTO contracts VALUES ('C3', '2026-03-10', '2026-04-09', 3100);

WITH RECURSIVE months(month_start) AS (
  SELECT date('2026-01-01')
  UNION ALL
  SELECT date(month_start, '+1 month') FROM months WHERE month_start < date('2026-04-01')
),
-- months builds in passes: the base row is 2026-01-01, then each pass takes
-- the previous pass's row and builds the next month from it (date(month_start,
-- '+1 month')), re-running against months's own growing output, until a
-- pass's output fails the WHERE condition and the recursion stops.
month_ranges AS (
  SELECT month_start, date(month_start, '+1 month', '-1 day') AS month_end
  FROM months
),
term AS (
  SELECT
    contract_id, start_date, end_date, total_amount,
    julianday(end_date) - julianday(start_date) + 1 AS term_days
  FROM contracts
),
alloc AS (
  SELECT
    t.contract_id,
    mr.month_start,
    MAX(t.start_date, mr.month_start)                                 AS overlap_start,
    MIN(t.end_date,   mr.month_end)                                   AS overlap_end,
    julianday(MIN(t.end_date, mr.month_end))
      - julianday(MAX(t.start_date, mr.month_start)) + 1               AS overlap_days,
    t.total_amount, t.term_days
  FROM term t
  JOIN month_ranges mr
    ON t.start_date <= mr.month_end AND t.end_date >= mr.month_start
)
SELECT
  strftime('%Y-%m', month_start)                              AS month,
  contract_id, overlap_start, overlap_end, overlap_days,
  ROUND(total_amount / term_days, 4)                          AS daily_rate,
  ROUND((total_amount / term_days) * overlap_days, 2)         AS recognized_amount
FROM alloc
WHERE overlap_days > 0
ORDER BY month, contract_id;

-- Portfolio total recognized revenue per month
-- (CTE scope is per statement: repeat the WITH clause when running this standalone)
WITH RECURSIVE months(month_start) AS (
  SELECT date('2026-01-01')
  UNION ALL
  SELECT date(month_start, '+1 month') FROM months WHERE month_start < date('2026-04-01')
),
-- months builds in passes: the base row is 2026-01-01, then each pass takes
-- the previous pass's row and builds the next month from it (date(month_start,
-- '+1 month')), re-running against months's own growing output, until a
-- pass's output fails the WHERE condition and the recursion stops.
month_ranges AS (
  SELECT month_start, date(month_start, '+1 month', '-1 day') AS month_end
  FROM months
),
term AS (
  SELECT
    contract_id, start_date, end_date, total_amount,
    julianday(end_date) - julianday(start_date) + 1 AS term_days
  FROM contracts
),
alloc AS (
  SELECT
    t.contract_id,
    mr.month_start,
    MAX(t.start_date, mr.month_start)                                 AS overlap_start,
    MIN(t.end_date,   mr.month_end)                                   AS overlap_end,
    julianday(MIN(t.end_date, mr.month_end))
      - julianday(MAX(t.start_date, mr.month_start)) + 1               AS overlap_days,
    t.total_amount, t.term_days
  FROM term t
  JOIN month_ranges mr
    ON t.start_date <= mr.month_end AND t.end_date >= mr.month_start
)
SELECT
  strftime('%Y-%m', month_start)                                    AS month,
  ROUND(SUM((total_amount / term_days) * overlap_days), 2)          AS recognized_revenue
FROM alloc
WHERE overlap_days > 0
GROUP BY month
ORDER BY month;

Result:

┌─────────┬─────────────┬───────────────┬─────────────┬──────────────┬────────────┬────────────────────┐
│  month  │ contract_id │ overlap_start │ overlap_end │ overlap_days │ daily_rate │ recognized_amount  │
├─────────┼─────────────┼───────────────┼─────────────┼──────────────┼────────────┼────────────────────┤
│ 2026-01 │ C1          │ 2026-01-15    │ 2026-01-31  │ 17.0         │ 100.0      │ 1700.0             │
│ 2026-02 │ C1          │ 2026-02-01    │ 2026-02-28  │ 28.0         │ 100.0      │ 2800.0             │
│ 2026-02 │ C2          │ 2026-02-01    │ 2026-02-28  │ 28.0         │ 100.0      │ 2800.0             │
│ 2026-03 │ C1          │ 2026-03-01    │ 2026-03-14  │ 14.0         │ 100.0      │ 1400.0             │
│ 2026-03 │ C3          │ 2026-03-10    │ 2026-03-31  │ 22.0         │ 100.0      │ 2200.0             │
│ 2026-04 │ C3          │ 2026-04-01    │ 2026-04-09  │ 9.0          │ 100.0      │ 900.0              │
└─────────┴─────────────┴───────────────┴─────────────┴──────────────┴────────────┴────────────────────┘

-- Portfolio total recognized revenue per month --
┌─────────┬────────────────────┐
│  month  │ recognized_revenue │
├─────────┼────────────────────┤
│ 2026-01 │ 1700.0             │
│ 2026-02 │ 5600.0             │
│ 2026-03 │ 3600.0             │
│ 2026-04 │ 900.0              │
└─────────┴────────────────────┘

Contract C1 runs January 15 through March 14, a 59-day, $5,900 term, exactly $100/day. January gets 17 days ($1,700, Jan 15-31), February gets the full 28 days ($2,800), March gets 14 days ($1,400, March 1-14). Those three pieces sum back to exactly $5,900, confirming no revenue was lost or double-counted at the boundaries. C3 (March 10 to April 9, $3,100, also $100/day) similarly splits into $2,200 in March and $900 in April. Portfolio totals per month: January $1,700; February $5,600 (C1 + C2); March $3,600 (C1's tail plus C3's start); April $900 (C3's tail).

The same technique, reframed

This overlap-day allocation is a general tool, not a one-off trick for "recognized revenue per month." The identical technique answers several sibling questions you may be asked instead:

• Deferred revenue balance: instead of summing recognized days, sum the unrecognized days remaining on each contract as of a snapshot date (term_days minus days elapsed so far), times daily_rate. This gives the balance-sheet liability at that point in time, not the income-statement flow.
• Forward-looking forecast: run the exact same monthly bucketing, but for months that haven't happened yet, to project how much of each currently-signed contract's revenue will recognize in future periods.
• ACV and backlog bucketing: the same straight-line allocation idea applied to annual contract value (ACV) or to backlog, spreading the total contracted value into monthly (or quarterly) buckets the same overlap-based way, rather than into GAAP-recognized revenue specifically.

Trade-offs & pitfalls

• Complexity: the month-generation CTE is O(months in range); the contract-month join is O(contracts x overlapping months), cheap for a normal-sized contract book, but a contract with a multi-year term joins against many month rows, worth watching if contracts can run 5+ years.
• Edge cases: a contract that starts and ends within the same month (overlap_days equals the full term_days; the join condition start <= month_end AND end >= month_start still catches it correctly); a 1-day contract term (term_days = 1, daily_rate = total_amount, no special case needed, the formula still works); contract amendments that change total_amount or dates mid-term retroactively (this model assumes the current row is authoritative for the whole historical term; a real system needs an effective-dated amendments table, not just an in-place UPDATE).
• Common wrong turn: prorating by months instead of by days, that is, dividing total_amount evenly across however many calendar months the contract touches, ignoring that touching a month for 3 days versus 28 days shouldn't recognize the same amount. This is the single most common shortcut that produces materially wrong numbers for any contract with a mid-month boundary.
• Reconciliation: this recognized-revenue schedule should tie out to the deferred revenue balance on the general ledger (unrecognized-to-date should always equal total contract value minus recognized-to-date). If it doesn't reconcile, look first for cents-level rounding applied per row rather than aggregating everything before rounding once.

Direct answer

Assign each user to a signup week (a fixed Monday-start bucket derived from signup_date), do the same week-bucketing to their events, then for each cohort week report the percentage of that cohort with at least one event in cohort-week-plus-N, for each N you care about. The two things that make this correct rather than approximately correct: bucketing both signup and event dates with the exact same week-start rule, and computing week offset as a count of week boundaries crossed, not a raw day difference divided by 7.

Structured elaboration

1. Cohort assignment: truncate each user's signup_date to the Monday that starts its week.
2. Event bucketing: apply the identical week-truncation rule to every event's date, so "week 1" for a cohort always means the same calendar week regardless of which day inside it the signup or event happened to land on.
3. Offset computation: week_offset = (event_week - signup_week) / 7 days, an integer.
4. Percent retained: for each cohort week and offset, COUNT(DISTINCT users active at that offset) / cohort_size.

Worked example (executed, sqlite3), including a real bug caught while verifying this answer

Seed data: cohort A signs up the week of 2025-06-02 (a Monday, 4 users), cohort B the week of 2025-06-09 (3 users), with deliberately uneven activity across the following two weeks.

CREATE TABLE users (user_id INTEGER PRIMARY KEY, signup_date TEXT);
INSERT INTO users VALUES
  (1,'2025-06-02'), (2,'2025-06-03'), (3,'2025-06-05'), (4,'2025-06-07'),
  (5,'2025-06-09'), (6,'2025-06-10'), (7,'2025-06-13');

CREATE TABLE events (user_id INTEGER, event_date TEXT);
INSERT INTO events VALUES
  (1,'2025-06-02'), (1,'2025-06-10'), (1,'2025-06-17'),
  (2,'2025-06-03'), (2,'2025-06-11'),
  (3,'2025-06-05'),
  (4,'2025-06-07'), (4,'2025-06-20'),
  (5,'2025-06-09'), (5,'2025-06-18'),
  (6,'2025-06-10'),
  (7,'2025-06-13'), (7,'2025-06-19');

First attempt at week-truncation used SQLite's built-in weekday N date modifier:

-- Looked correct, but had a real trap: 'weekday 1' only ADVANCES to the next
-- Monday; if the input date IS ALREADY Monday, it leaves it unchanged, so
-- stepping back '-7 days' afterward overshoots by a full extra week:
SELECT strftime('%w','2025-06-02') AS dow,                        -- 1 (Monday)
       date('2025-06-02','weekday 1')            AS naive_step1,   -- 2025-06-02 (unchanged)
       date('2025-06-02','weekday 1','-7 days')  AS naive_result;  -- 2025-05-26 (WRONG, a week early)

Running the full retention query with that formula put user 1 (who signed up on a Monday) into the wrong cohort week entirely, one week ahead of the other three users who signed up later that same week. That is caught here specifically because the seed data includes a signup that lands exactly on the week boundary, the case most naive week-truncation logic gets wrong.

Corrected week-start formula, using day-of-week arithmetic instead of the modifier:

-- strftime('%w', d) is 0=Sunday..6=Saturday; (%w + 6) % 7 = days since Monday
date(d, '-' || ((strftime('%w', d) + 6) % 7) || ' days')

Full corrected query:

WITH cohorts AS (
  SELECT user_id, date(signup_date, '-' || ((strftime('%w', signup_date) + 6) % 7) || ' days') AS signup_week
  FROM users
),
cohort_sizes AS (SELECT signup_week, COUNT(*) AS cohort_size FROM cohorts GROUP BY signup_week),
event_weeks AS (
  SELECT user_id, date(event_date, '-' || ((strftime('%w', event_date) + 6) % 7) || ' days') AS event_week
  FROM events
),
activity AS (
  SELECT c.signup_week, c.user_id,
    CAST((julianday(e.event_week) - julianday(c.signup_week)) / 7 AS INTEGER) AS week_offset
  FROM cohorts c JOIN event_weeks e ON e.user_id = c.user_id
  WHERE e.event_week >= c.signup_week
)
SELECT cs.signup_week, cs.cohort_size,
  ROUND(100.0*COUNT(DISTINCT CASE WHEN a.week_offset=0 THEN a.user_id END)/cs.cohort_size,1) AS pct_week0,
  ROUND(100.0*COUNT(DISTINCT CASE WHEN a.week_offset=1 THEN a.user_id END)/cs.cohort_size,1) AS pct_week1,
  ROUND(100.0*COUNT(DISTINCT CASE WHEN a.week_offset=2 THEN a.user_id END)/cs.cohort_size,1) AS pct_week2
FROM cohort_sizes cs LEFT JOIN activity a ON a.signup_week = cs.signup_week
GROUP BY cs.signup_week, cs.cohort_size ORDER BY cs.signup_week;

Result:

Every user has an event in their own signup week by construction, so week 0 is correctly 100.0% for both cohorts once the truncation bug is fixed. Cohort A: users 1 and 2 return in week 1 (2/4 = 50.0%); users 1 and 4 return in week 2 (2/4 = 50.0%). Cohort B: users 5 and 7 return in week 1 (2/3 = 66.7%); nobody returns in week 2 (0.0%), matching the seeded data exactly.

Trade-offs and pitfalls

• The week-truncation trap above is a genuine date-boundary off-by-one, the same failure family as a date_trunc cohort bug: it is invisible for signups in the middle of a week and only surfaces for a signup that lands exactly on the week-start day, so a test suite needs at least one such boundary case to catch it.
• Extending to day 1 through day 30: the same activity CTE pattern generalizes by computing offset in days instead of weeks (julianday(event_date) - julianday(signup_date)), but doing this efficiently for many offsets at once favors a single pass that buckets every event into its offset once, rather than a separate correlated subquery per offset day.
• Timezone-adjusted cohorts: if signup_date/event_date are UTC timestamps and users span timezones, the week boundary itself should be computed in each user's local time (the same per-user AT TIME ZONE pattern used for daily bucketing), otherwise a user near a week boundary can be misassigned by a few hours the same way a naive UTC-date bucket misassigns daily reports.
• Cohorts with very small cohort_size (a handful of users) produce noisy percentages, a single user churning swings the rate by double-digit points; report the raw cohort_size alongside the percentage so a reader does not over-trust a 50.0% built from 2 out of 4 users.

Direct answer

NULL means "unknown," not a value, so it doesn't equal anything, not even another NULL. col = NULL always evaluates to NULL (treated as false in WHERE), which is why col != 'x' also silently drops rows where col is NULL: NULL != 'x' is NULL, not true. You have to test for it explicitly with IS NULL / IS NOT NULL. COALESCE(expr1, expr2, ...) returns the first non-NULL argument, commonly used to substitute a default.

Structured elaboration

SQL uses three-valued logic: TRUE, FALSE, and UNKNOWN. Any comparison involving NULL (=, !=, <, >) evaluates to UNKNOWN, and a WHERE clause only keeps rows where the condition is TRUE, so UNKNOWN rows are silently dropped, exactly like FALSE rows, with no error.

Aggregates mostly ignore NULLs rather than propagating the UNKNOWN logic: SUM, AVG, MAX, MIN skip NULL values, and COUNT(column) counts only non-NULL values in that column, while COUNT(*) counts rows regardless of NULLs.

Worked example (sqlite3, verified)

Sample data:

CREATE TABLE users (user_id INTEGER PRIMARY KEY, email TEXT);
INSERT INTO users VALUES (1, 'alice@x.com'), (2, NULL), (3, 'carol@x.com');

CREATE TABLE payments (payment_id INTEGER PRIMARY KEY, user_id INTEGER, amount INTEGER);
INSERT INTO payments VALUES (1, 1, 100), (2, 1, NULL), (3, 2, 50), (4, 3, 75), (5, 3, NULL);

= NULL versus IS NULL:

SELECT COUNT(*) FROM users WHERE email = NULL;    -- 0 rows
SELECT COUNT(*) FROM users WHERE email IS NULL;    -- 1 row

Verified: 0 matches for = NULL, 1 match for IS NULL (user 2).

!= silently drops the NULL row:

SELECT user_id, email FROM users WHERE email != 'alice@x.com';

Result: only (3, 'carol@x.com'). User 2 (NULL email) is not "not equal to alice," it's UNKNOWN, so it never appears, even though intuitively a missing email is certainly different from a specific address.

COUNT(*) vs COUNT(column):

SELECT COUNT(*) AS row_count, COUNT(amount) AS non_null_amount_count, SUM(amount) AS total
FROM payments;

Result: (5, 3, 225). There are 5 payment rows, but only 3 have a non-NULL amount, and SUM adds only those 3 (100 + 50 + 75 = 225), silently ignoring the 2 NULLs rather than making the whole sum NULL.

COALESCE for defaults:

SELECT payment_id, amount, COALESCE(amount, 0) AS amount_or_zero FROM payments ORDER BY payment_id;

Result: payments 2 and 5 (NULL amount) show amount_or_zero = 0, everything else passes through unchanged.

NULL behavior after a LEFT JOIN (fan-out and double counting). Aggregating right after a multi-table LEFT JOIN is a common trap when a customer has more than one matching row on another joined table. Sample data:

CREATE TABLE customers (customer_id INTEGER PRIMARY KEY, name TEXT);
INSERT INTO customers VALUES (1, 'Alice'), (2, 'Bob');

CREATE TABLE orders (order_id INTEGER PRIMARY KEY, customer_id INTEGER, amount INTEGER);
INSERT INTO orders VALUES (101, 1, 50), (102, 1, 30), (103, 2, 20);

CREATE TABLE refunds (refund_id INTEGER PRIMARY KEY, customer_id INTEGER, amount INTEGER);
INSERT INTO refunds VALUES (201, 1, 10), (202, 1, 5);

Alice has 2 orders and, separately, 2 refunds. Joining both to customers in one query multiplies her order rows by her refund count:

SELECT c.customer_id, COUNT(o.order_id) AS order_rows_seen, SUM(o.amount) AS sum_order_amount_wrong
FROM customers c
LEFT JOIN orders o ON o.customer_id = c.customer_id
LEFT JOIN refunds r ON r.customer_id = c.customer_id
GROUP BY c.customer_id;

Result: Alice shows order_rows_seen = 4 and sum_order_amount_wrong = 160, double the true values (2 orders, $80), because each of her 2 orders got paired with each of her 2 refunds (2x2 = 4 rows). Bob (1 order, 0 refunds, so no fan-out) correctly shows 1 and 20.

Fix, pre-aggregate orders before joining anything else:

WITH order_agg AS (
  SELECT customer_id, COUNT(*) AS order_count, SUM(amount) AS sum_order_amount
  FROM orders GROUP BY customer_id
)
SELECT c.customer_id, oa.order_count, oa.sum_order_amount
FROM customers c LEFT JOIN order_agg oa ON oa.customer_id = c.customer_id;

Result: Alice correctly shows (1, 2, 80). Note AVG(o.amount) in the broken version happened to still read 40.0, the same as the correct average, because Alice's duplication factor was uniform (every order duplicated exactly twice); that's a coincidence of this data, not something to rely on. COUNT and SUM are the numbers that reveal the bug reliably.

Trade-offs & pitfalls

• Never write = NULL or != NULL; both silently return zero rows and produce no error, which makes this bug easy to ship unnoticed.
• NOT IN (subquery) is a related NULL trap: if the subquery returns even one NULL, NOT IN returns no rows at all for every outer row, because comparing against a NULL element makes the whole NOT IN expression UNKNOWN. Prefer NOT EXISTS when the inner column can contain NULLs.
• When aggregating after joining multiple one-to-many relationships in a single query, pre-aggregate each one-to-many table separately (as in the fix above) before joining, or use COUNT(DISTINCT ...) as a partial guard, though DISTINCT alone won't fix a corrupted SUM.
• GROUP BY treats all NULLs as one group (they're considered equal for grouping purposes, unlike in a WHERE ... = NULL comparison), and DISTINCT does the same, collapsing multiple NULLs into a single NULL row.

Direct answer

Aggregate the numerator and the denominator from their own source tables independently, then combine with an OUTER join so a day missing from one side doesn't silently vanish from the result, and guard the division with NULLIF/COALESCE so a missing-data day (NULL) and a genuine zero-denominator day (0) are represented differently instead of being conflated into the same output.

Structured elaboration

Approach, two equivalent framings:

JOIN/CTE: aggregate each source once into its own CTE, then FULL OUTER JOIN them on the shared day column, so a day present in either source survives.

Correlated subquery (the alternate framing named in this topic's other subquery question): instead of aggregating each source into a CTE and joining, drive off a list of distinct days and pull each metric with a scalar subquery correlated to that day. Both approaches are demonstrated below and were run to confirm they produce identical results; they differ in mechanics and performance, not in meaning.

Key points on the NULL/zero guard:

• Both sides NULL (day missing from both sources): rate should be NULL, meaning "no data," not zero.
• Denominator NULL or 0: NULLIF(active_users, 0) turns a 0 into NULL before dividing, avoiding a divide-by-zero error and correctly reporting "undefined," not a fabricated 0.
• Numerator NULL (day tracked for active users but conversions weren't recorded that day): division by a NULL numerator also yields NULL through ordinary NULL propagation, which is usually the right behavior, but is easy to miss if you only tested the "both NULL" case explicitly.

Worked example

Seed data: daily_conversions is missing 2026-05-04 entirely (no conversions recorded, though users were active); daily_active_users explicitly records 0 active users on 2026-05-06 (a real zero, not missing data); 2026-05-05 is absent from both tables (no data collected at all that day).

JOIN/CTE version:

SELECT
  day, conversions, active_users,
  CASE
    WHEN conversions IS NULL AND active_users IS NULL THEN NULL
    WHEN COALESCE(active_users, 0) = 0 THEN 0.0
    ELSE conversions::DOUBLE / NULLIF(active_users, 0)
  END AS conversion_rate
FROM (
  SELECT
    COALESCE(c.day, a.day) AS day,
    c.conversions,
    a.active_users
  FROM daily_conversions c
  FULL OUTER JOIN daily_active_users a
    ON c.day = a.day
) t
ORDER BY day;

Result (run in DuckDB):

day        | conversions | active_users | conversion_rate
-----------+-------------+--------------+--------------------
2026-05-01 |          12 |          200 |                0.06
2026-05-02 |          15 |          210 | 0.07142857142857142
2026-05-03 |           9 |          180 |                0.05
2026-05-04 |        None |          190 |                None
2026-05-06 |           5 |            0 |                 0.0

Correlated-subquery version, same result:

WITH all_days AS (
  SELECT day FROM daily_conversions
  UNION
  SELECT day FROM daily_active_users
)
SELECT
  d.day,
  (SELECT c.conversions FROM daily_conversions c WHERE c.day = d.day) AS conversions,
  (SELECT a.active_users FROM daily_active_users a WHERE a.day = d.day) AS active_users,
  CASE
    WHEN (SELECT c.conversions FROM daily_conversions c WHERE c.day = d.day) IS NULL
     AND (SELECT a.active_users FROM daily_active_users a WHERE a.day = d.day) IS NULL THEN NULL
    WHEN COALESCE((SELECT a.active_users FROM daily_active_users a WHERE a.day = d.day), 0) = 0 THEN 0.0
    ELSE (SELECT c.conversions FROM daily_conversions c WHERE c.day = d.day)::DOUBLE
         / NULLIF((SELECT a.active_users FROM daily_active_users a WHERE a.day = d.day), 0)
  END AS conversion_rate
FROM all_days d
ORDER BY d.day;

Result: identical, row for row, to the JOIN/CTE version above. Note 2026-05-05 correctly does not appear in either result, since it's absent from both source tables entirely and all_days (the UNION of both day columns) never includes it.

Complexity

The JOIN/CTE version aggregates each source table exactly once, O(n + m) for n rows in daily_conversions and m rows in daily_active_users, then a single merge join or hash join on day. The correlated-subquery version re-evaluates each scalar subquery once per row of all_days, so it's effectively O(d * (n + m)) for d distinct days unless the query planner is able to rewrite it into a join internally (some optimizers do this, many don't guarantee it). For a handful of days this difference is invisible; for a day list derived from a much larger driving table, the JOIN/CTE form scales far better.

Edge cases

• Day present in neither source: correctly absent from the output entirely in both framings (not shown as a NULL row), since there's no day value to attach a NULL rate to.
• Day present with an explicit 0 in the denominator (2026-05-06 above): must show rate 0.0, not NULL, since the data affirmatively says zero active users, not "unknown."
• Day present in the denominator but missing from the numerator source (2026-05-04): the direct-answer table shows NULL for this, driven by ordinary NULL-propagation through the division, not by the explicit "both NULL" CASE branch; worth testing explicitly rather than assuming the CASE statement's comments describe every path correctly.

Trade-offs & pitfalls

• NULL vs 0 conflation is the single most common bug in ratio metrics. A NULL rate on a chart usually renders as a visual gap; a 0% rate renders as a real, alarming data point. Getting this backwards misleads whoever is reading the dashboard.
• Not every engine supports FULL OUTER JOIN (MySQL historically didn't); the portable emulation is a UNION of a LEFT JOIN and a RIGHT JOIN (or two LEFT JOINs with roles swapped), which is worth knowing even when the target engine does support it natively.
• Prefer the JOIN/CTE framing by default; reach for the correlated-subquery framing mainly when it's genuinely more readable for a one-off ad hoc query over a small, fixed set of days, not as the default pattern for a query that will run repeatedly at scale.

Direct answer

Filter orders to the last 12 months, group by user_id, and use HAVING to require both COUNT(*) >= 3 and an average-order-value threshold, computed as AVG(amount_cents) / 100.0 since the amount is stored in cents. Both conditions are aggregate conditions, so both belong in HAVING, joined with AND.

Approach

1. Filter to the relevant window first (WHERE created_at in the last 12 months), so only recent orders count toward frequency and AOV.
2. Group by user_id.
3. In HAVING, require COUNT(*) >= 3 for frequency and AVG(amount_cents) / 100.0 > threshold for average order value, in the same clause since both are per-group aggregate conditions.

Worked example (sqlite3, verified)

Sample data (reference "now" pinned to 2024-04-01, so the last 12 months = [2023-04-01, 2024-04-01)):

CREATE TABLE orders (order_id INTEGER PRIMARY KEY, user_id INTEGER, amount_cents INTEGER, created_at TEXT);
INSERT INTO orders VALUES
  (1, 1, 6000, '2024-01-05'),
  (2, 1, 5500, '2024-02-10'),
  (3, 1, 7000, '2024-03-01'),   -- user 1: 3 orders, AOV ~= $61.67
  (4, 2, 2000, '2024-01-15'),
  (5, 2, 1500, '2024-02-20'),
  (6, 2, 1000, '2024-03-05'),   -- user 2: 3 orders, AOV = $15.00 (fails threshold)
  (7, 3, 9000, '2024-01-01'),
  (8, 3, 8000, '2024-02-01'),   -- user 3: only 2 orders (fails count)
  (9, 4, 12000, '2023-01-01');  -- user 4: outside the 12-month window

Query, at least 3 orders in the window and AOV above $50:

SELECT user_id, COUNT(*) AS order_count, AVG(amount_cents) / 100.0 AS aov_usd
FROM orders
WHERE created_at >= '2023-04-01' AND created_at < '2024-04-01'
GROUP BY user_id
HAVING COUNT(*) >= 3 AND AVG(amount_cents) / 100.0 > 50
ORDER BY user_id;

Result: exactly 1 row, (1, 3, 61.666666666666664).

Full per-user breakdown (no HAVING filter, to show why each other user was excluded):

SELECT user_id, COUNT(*) AS order_count, AVG(amount_cents) / 100.0 AS aov_usd
FROM orders
WHERE created_at >= '2023-04-01' AND created_at < '2024-04-01'
GROUP BY user_id
ORDER BY user_id;

Result: (1, 3, 61.67), (2, 3, 15.0), (3, 2, 85.0). User 2 has enough orders (3) but fails AOV ($15 < $50). User 3 clears AOV easily ($85) but only has 2 orders, failing the count threshold. User 4's single order predates the window entirely and never appears in either result.

Key points

• Both filter conditions on the group (order count, AOV) are aggregate conditions, so both must be in HAVING; there's no valid way to express either one in WHERE.
• Storing money in cents and dividing by 100.0 in the SELECT (not earlier) avoids integer-division truncation in engines where integer/integer division floors the result; 100.0 forces floating-point division.
• The date filter in WHERE happens before grouping, which is both correct (it must, since "last 12 months" is a per-row property, not a per-user aggregate) and efficient (fewer rows reach GROUP BY).

Complexity, edge cases & pitfalls

• An index on (user_id, created_at) lets the engine narrow to each user's recent orders efficiently before aggregating; without it, this query does a full scan of orders.
• If amount_cents can be NULL for some rows, AVG silently ignores those rows rather than erroring, which quietly changes both the order count used for HAVING's COUNT(*) (still counts the row) and the AOV (excludes it), a subtle mismatch worth guarding with WHERE amount_cents IS NOT NULL if NULL amounts are a data quality issue rather than a valid zero-dollar order.
• The "last 12 months" boundary is a common off-by-one risk: this answer defines it as [2023-04-01, 2024-04-01), a full rolling year ending the day before the reference date; using >= on the lower bound and strict < on the upper bound (rather than <= on a date that might include a partial day) avoids double-counting the boundary day.
• Requiring COUNT(*) >= 3 in the same window as the AOV means a user just barely at 3 orders has their average pulled by exactly those 3 values, small samples can look extreme; that's expected here and worth flagging to a stakeholder if the tier is meant to reward high-value repeat customers rather than accidentally reward small-sample noise.