SQL Conversion Rate Interview Questions: Eligibility, Grain, and Denominator Traps

SQL conversion rate interview questions are denominator questions before they are division questions. Define who is eligible, what one opportunity represents, and when that opportunity ends. Then count eligible units with at least one qualifying outcome. Counting purchases and dividing by users can measure purchase frequency instead of the probability of conversion.

The original example below goes deeper on a narrower problem: the same records produce a 50% user conversion rate, a 40% session conversion rate, and 0.6 orders per session. None is interchangeable with the others.

What belongs in the conversion denominator?

An eligible unit is an entity that had the opportunity specified by the metric. It might be an assigned experiment participant, an account offered a trial, a session entering checkout, or an impression that could receive a click. An outcome table alone usually cannot describe all those opportunities, because unsuccessful units may have no outcome row.

Ask five questions before coding: Which population enters? What identifies one unit? Which outcome qualifies? When does its observation window close? Which exclusions are allowed? Write the answers as a sentence, not a collection of convenient filters.

For our exercise: “Among non-bot users whose seven-day window is fully observed by August 10, what fraction completed at least one purchase during the seven elapsed days beginning at eligibility?” A user who never opens a session remains eligible. The denominator does not shrink merely because that user was inactive.

This is a custom cohort metric, not a claim about a dashboard default. Google Analytics separately documents user and session key-event rates; their denominators differ by measurement unit. Always read the named product's definition before trying to reconcile it with warehouse SQL. Google Analytics dimensions and metrics

One dataset, three valid measurements

Users 1–4 are eligible. User 1 has three sessions and two orders in one session. User 2 has one session without a qualifying order. User 3 has one session and one order. User 4 never starts a session. Therefore two of four users convert, but only two of five sessions convert.

Three orders divided by five sessions equals 0.6 orders per session. It is not a 60% session conversion probability: two purchases in one session increase frequency but do not create a second converted session. More purchases could push orders per session above one without any arithmetic error.

The user and session rates answer different business questions. The user metric gives every eligible person one opportunity; the session metric gives frequent visitors more opportunities. A product change that increases sessions can alter the session denominator even if the number of purchasing users stays fixed. Explain that change before interpreting a lower session rate as worse customer behavior.

Load the eligibility, session, and order fixture

The runnable SQL uses DuckDB 1.5.2. Timestamps represent UTC; the window is seven elapsed days, not seven local calendar dates. All listed orders are completed. Session IDs are authoritative here, and each session is fully contained inside its user's observation window. These simplifying assumptions must be reconsidered for production data.

User 5 is a bot. User 6 joined too recently. Order 3 is logged twice, order 4 is exactly at user 2's excluded upper boundary, and order 5 predates user 4's eligibility. Null session IDs on those two out-of-window orders do not affect this fixture's valid-session calculation.

CREATE TABLE users (
 user_id INTEGER PRIMARY KEY,
 eligible_at TIMESTAMP,
 is_bot BOOLEAN
);
INSERT INTO users VALUES
 (1,'2026-08-01',false),(2,'2026-08-01',false),
 (3,'2026-08-01',false),(4,'2026-08-01',false),
 (5,'2026-08-01',true),(6,'2026-08-09',false);

CREATE TABLE sessions (
 session_id INTEGER PRIMARY KEY,
 user_id INTEGER,
 starts_at TIMESTAMP,
 ends_at TIMESTAMP
);
INSERT INTO sessions VALUES
 (11,1,'2026-08-02 10:00','2026-08-02 11:00'),
 (12,1,'2026-08-03 10:00','2026-08-03 11:00'),
 (13,1,'2026-08-04 10:00','2026-08-04 11:00'),
 (21,2,'2026-08-02 10:00','2026-08-02 11:00'),
 (31,3,'2026-08-03 10:00','2026-08-03 11:00');

CREATE TABLE order_log (
 order_id INTEGER,
 user_id INTEGER,
 session_id INTEGER,
 purchased_at TIMESTAMP
);
INSERT INTO order_log VALUES
 (1,1,11,'2026-08-02 10:10'),
 (2,1,11,'2026-08-02 10:20'),
 (3,3,31,'2026-08-03 10:10'),
 (3,3,31,'2026-08-03 10:10'),
 (4,2,NULL,'2026-08-08'),
 (5,4,NULL,'2026-07-31'),
 (6,5,NULL,'2026-08-02'),
 (7,6,NULL,'2026-08-09 12:00');

The schema separates eligibility from activity deliberately. Starting from sessions would erase user 4 before the user-level question even begins. Starting from orders would erase every nonconverter. Neither missing population can be recovered by a clever division at the end.

Preserve one eligible user before counting outcomes

Construct the mature cohort first, then attach a binary flag with EXISTS. PostgreSQL's documentation describes EXISTS as testing whether a subquery returns any rows; multiple matching rows do not multiply the outer row. That behavior fits an “at least one purchase” outcome. PostgreSQL subquery expressions

CREATE VIEW eligible_users AS
SELECT user_id, eligible_at,
       eligible_at + INTERVAL '7 days' AS closes_at
FROM users
WHERE is_bot = false
  AND eligible_at + INTERVAL '7 days'
      <= TIMESTAMP '2026-08-10';

CREATE VIEW user_outcomes AS
SELECT u.user_id,
       CASE WHEN EXISTS (
         SELECT 1 FROM order_log o
         WHERE o.user_id = u.user_id
           AND o.purchased_at >= u.eligible_at
           AND o.purchased_at < u.closes_at
       ) THEN 1 ELSE 0 END AS converted
FROM eligible_users u;

SELECT COUNT(*) AS eligible_users,
       SUM(converted) AS converted_users,
       1.0 * SUM(converted) / NULLIF(COUNT(*),0)
         AS user_conversion_rate
FROM user_outcomes;

The result is (4, 2, 0.5). User flags in ID order are 1, 0, 1, 0. The duplicated order cannot change a binary existence test. User 6's observed purchase does not justify including that user early: selecting immature users only when they already converted would condition eligibility on the outcome.

The lower bound is inclusive and the upper bound exclusive. At exactly August 8, user 2's purchase belongs outside this seven-day window. A purchase one second earlier would qualify. Keep that rule explicit instead of using an inclusive BETWEEN and discovering the disagreement during reconciliation.

Change the unit explicitly for a session metric

For session conversion, create one row per qualifying session and attach the number of distinct orders observed inside it. The join matches both session and user identity and enforces session-time boundaries. This prevents a mismatched session key from silently assigning another user's purchase.

CREATE VIEW session_outcomes AS
SELECT s.session_id,
       COUNT(DISTINCT o.order_id) AS orders
FROM eligible_users u
JOIN sessions s
  ON s.user_id = u.user_id
 AND s.starts_at >= u.eligible_at
 AND s.ends_at <= u.closes_at
 AND s.ends_at > s.starts_at
LEFT JOIN order_log o
  ON o.user_id = s.user_id
 AND o.session_id = s.session_id
 AND o.purchased_at >= s.starts_at
 AND o.purchased_at < s.ends_at
GROUP BY s.session_id;

SELECT COUNT(*) AS eligible_sessions,
       SUM(CASE WHEN orders > 0 THEN 1 ELSE 0 END)
         AS converted_sessions,
       1.0 * SUM(CASE WHEN orders > 0 THEN 1 ELSE 0 END)
         / NULLIF(COUNT(*),0) AS session_conversion_rate,
       1.0 * SUM(orders) / NULLIF(COUNT(*),0)
         AS orders_per_session
FROM session_outcomes;

The result is (5, 2, 0.4, 0.6). Session order counts are 2, 0, 0, 0, 1. The duplicate order ID disappears from the frequency metric too. In production, conflicting versions of one order require a canonicalization rule; COUNT(DISTINCT) does not resolve contradictory user IDs, statuses, or timestamps.

A session crossing the cohort boundary is excluded under this exercise's full-containment rule. Another contract might include sessions starting before the boundary and observe them until their own end. State the alternative and its observation requirement rather than silently changing a join predicate.

Where joins manufacture a plausible wrong answer

Joining users to sessions and orders independently on user_id creates a many-to-many product. User 1's two orders can appear across all three sessions. A distinct-user numerator may survive that fanout while COUNT(*), revenue, or session counts do not. Mixing one repaired aggregate with another inflated aggregate is particularly hard to spot.

Keep a separate relation for each requested grain, aggregate it there, and combine summaries only after their keys are unique. Assert COUNT(*) = COUNT(DISTINCT unit_id) at the analysis boundary. Do not rely on a final SELECT DISTINCT to repair numbers already aggregated over duplicated rows.

Missing outcomes also need care. A right-table filter in WHERE after a left join can remove nonconverters. Put outcome eligibility in the join condition or use the existence pattern above. For a zero denominator, return an undefined rate such as NULL, with the population count alongside it; zero percent means opportunities existed but none converted.

Segment rates are not automatically additive

Suppose one disjoint segment converts 1 of 2 users and another converts 9 of 90. The unweighted average of their rates is 30%, but the combined user conversion rate is 10 / 92, approximately 10.87%. Recombine numerators and denominators when the target is the pooled population.

That repair requires nonoverlapping segments at the chosen grain. A user browsing both mobile and desktop can appear in both event-property groups. Summing those denominators double-counts people. Amplitude explicitly notes that a user may appear in multiple grouped rows; its documentation is a useful reminder to check group membership before treating a displayed total as a sum. Amplitude multi-dimensional analysis

Choose a fixed assignment such as platform at eligibility, or compute the overall distinct population separately. Neither choice is universally correct: one answers acquisition-platform performance, while another describes activity across platforms. Naming the dimension's time and scope is part of the SQL answer.

Tests that expose denominator mistakes

The article's three SQL blocks were executed together in DuckDB 1.5.2. Verify the baseline counts before optimizing. Duplicate every order-log row: all three measurements should remain unchanged. Add a third unique order to session 11: user and session conversion stay at 50% and 40%, while orders per session rises to 0.8.

Move user 2's boundary purchase one second earlier: user conversion becomes 75%, while session conversion stays 40% because the order has no qualifying session. This deliberate disagreement tests the two contracts independently. Remove all users and both rates become NULL, not a divide-by-zero error.

Finally, compare eligibility counts before and after every join and report exclusions separately: one bot, one immature user, one pre-eligibility order, and one upper-boundary order in the original fixture. In a pipeline, distinguish complete event-time coverage from merely having a recent ingestion timestamp; late orders can revise a mature cohort's observed result.

Practice conversion contracts, not just formulas

Read each prompt's population and time window before borrowing the code above; they are related practice, not claims about an employer's current interview process.

Question	Practice focus
Compute Company Suggestion Funnels	Funnel entry.
Compute signup rate and retention from raw logs	Cohort rules.
Write SQL for retention, conversion, and churn	User grain.
Query conversion and retention with SQL windows	Time bounds.
Compute ad impression conversion rates	Unit keys.

Open Compute Company Suggestion Funnels and write the denominator contract before the query. For the separate question of which touchpoint receives conversion credit, continue with SQL attribution interview questions.

Sources and Further Reading

• Google Analytics: Dimensions and metrics
• PostgreSQL: Subquery expressions and EXISTS
• Amplitude: Multi-dimensional analysis with Data Tables