Vague SQL Interview Questions: Define a Testable Metric

Turn an ambiguous prompt into a testable metric

When an interviewer asks for a conversion rate, the missing definitions can matter more than the SQL syntax. State the output grain, qualifying event, observation window, and reporting cutoff before coding. This worked PostgreSQL 16 example answers one specific question: among users in fully observed signup-date cohorts, what percentage completed a purchase during the first 168 hours after signup?

Ask only questions that change the answer

“First purchase” can mean first purchase ever or first qualifying purchase after signup. “Seven days” can mean 168 elapsed hours or seven calendar dates. “By day” can group by signup date or purchase date. These are different metrics, not cosmetic choices. If the interviewer asks you to choose, state a reasonable definition and proceed instead of asking an endless series of questions.

Use four definitions

Grain: one row per UTC signup date. Metric: users with at least one completed purchase at or after signup and before signup plus 168 hours, divided by signup users. Time: a fixed exclusive as-of timestamp and fully mature daily cohorts. Identity: earliest observed signup per stable user ID; repeat purchases count once. Our timestamps are stored as UTC values in timestamp-without-time-zone columns for the fixture. Local-time applications need an explicit timezone conversion policy.

Explain the cohort cutoff precisely

As of October 17 at 00:00 UTC, the October 9 signup-date cohort is fully mature. Even a signup at October 9 at 23:59 has finished its seven-day window before the cutoff. October 10 is excluded as a whole. A user who signed up exactly at midnight on October 10 has individually matured, but including only that user would create a partial daily denominator. The condition cohort midnight plus eight days <= as_of prevents this inconsistency.

This example excludes the purchase exactly at the seven-day endpoint. It also ignores a completed purchase that predates signup and still finds a later valid purchase. Computing the earliest purchase over all history and then rejecting it would miss that valid conversion. If the actual requirement is first-ever-purchase conversion, ask how pre-signup purchases change eligibility instead of silently reinterpreting the metric.

Run the complete fixture and query

WITH params(as_of) AS (VALUES (TIMESTAMP '2026-10-17 00:00:00')),
events(user_id,event_name,status,event_ts) AS (
 VALUES
 (1,'signup','ok',TIMESTAMP '2026-10-01 09:00'),
 (1,'signup','ok',TIMESTAMP '2026-10-02 09:00'),
 (1,'purchase','completed',TIMESTAMP '2026-09-30 09:00'),
 (1,'purchase','completed',TIMESTAMP '2026-10-02 10:00'),
 (1,'purchase','completed',TIMESTAMP '2026-10-03 10:00'),
 (2,'signup','ok',TIMESTAMP '2026-10-01 09:00'),
 (2,'purchase','failed',TIMESTAMP '2026-10-02 09:00'),
 (2,'purchase','completed',TIMESTAMP '2026-10-08 09:00'),
 (3,'signup','ok',TIMESTAMP '2026-10-09 23:00'),
 (4,'signup','ok',TIMESTAMP '2026-10-10 00:00'),
 (4,'purchase','completed',TIMESTAMP '2026-10-11 00:00'),
 (5,'signup','ok',TIMESTAMP '2026-10-16 00:00')
), signups AS (
 SELECT e.user_id, MIN(e.event_ts) AS signup_ts
 FROM events e CROSS JOIN params p
 WHERE e.event_name='signup' AND e.event_ts < p.as_of
 GROUP BY e.user_id
), mature_signups AS (
 SELECT s.* FROM signups s CROSS JOIN params p
 WHERE s.signup_ts::date + INTERVAL '8 days' <= p.as_of
), user_result AS (
 SELECT s.user_id,s.signup_ts,
   EXISTS (
     SELECT 1 FROM events e CROSS JOIN params p
     WHERE e.user_id=s.user_id AND e.event_name='purchase'
       AND e.status='completed' AND e.event_ts >= s.signup_ts
       AND e.event_ts < s.signup_ts + INTERVAL '168 hours'
       AND e.event_ts < p.as_of
   ) AS converted
 FROM mature_signups s
)
SELECT signup_ts::date AS signup_date,COUNT(*) AS signup_users,
       COUNT(*) FILTER (WHERE converted) AS converted_users,
       ROUND(100.0*COUNT(*) FILTER (WHERE converted)/COUNT(*),2) AS rate_pct
FROM user_result GROUP BY signup_ts::date ORDER BY signup_date;

signup_date | signup_users | converted_users | rate_pct
2026-10-01 | 2 | 1 | 50.00
2026-10-09 | 1 | 0 | 0.00

User 1 converts once despite duplicate signup and purchase events; their pre-signup purchase is irrelevant to this definition. User 2 has a failed purchase and a completed purchase at the excluded endpoint, so does not convert. User 3 supplies the zero-conversion cohort. Users 4 and 5 belong to immature daily cohorts and do not enter either count. EXISTS keeps the user-level result to one row regardless of matching purchases.

Connect each query stage to an assumption

A concise explanation is: “I first select each user’s earliest signup, keep complete signup-date cohorts, evaluate conversion as a user-level Boolean, then aggregate. I use a fixed cutoff so the result can be reproduced.” For PostgreSQL semantics, see EXISTS and the date/time reference. The maturity condition is part of the complete query, not a reminder left for the reader to add later.

Use boundary users to test the definition

Check that converted_users never exceeds signup_users, each user has one anchor, and every reported cohort has its entire observation window available. Change the cutoff to October 16 at 00:00: the October 9 cohort must disappear. Move user 2’s completed purchase one second earlier: the October 1 rate must become 100%. These tests distinguish plausible-looking percentages from the stated contract.

Keep freshness separate from clock maturity

The query assumes the event feed is complete before as_of. Wall-clock time alone does not prove that late-arriving events have landed. In a real report, choose the cutoff from an agreed completeness watermark or show a provisional label. Do not compare a recent partial cohort with older mature cohorts under the same rate label. Likewise, a ratio of purchases occurring today to signups occurring today is not this cohort conversion rate.

FAQ

What if I must make assumptions?

State the metric in one sentence, name the boundary and duplicate rules, and make the query match them. Then identify which assumptions would need stakeholder confirmation.

Can I report in-progress cohorts?

Yes, but label them separately and show exposure or age. Do not silently mix their incomplete windows into a mature seven-day rate.

What should I practice next?

Use retention windows for calendar-day returns and ordered funnels when each step depends on a prior qualifying event.