Quick summary
Summarize this blog with AI
Data-scientist SQL questions should test analytical validity
This PostgreSQL 16 practice set focuses on four issues that change a scientific result: information available at prediction time, complete outcome windows, weighted denominators, and calendar-based rolling features. Every question has complete fictional input and an expected answer. The queries demonstrate data construction, not predictive performance or statistical significance.
1. Build a feature using only information known at the cutoff
For each user, count events and sum their amounts during the preceding seven days. An event must both occur and be available strictly before the prediction cutoff. All timestamps in this fixture represent UTC. Include the exact start boundary and exclude the cutoff itself.
WITH predictions(user_id,cutoff) AS (
VALUES (1,TIMESTAMP '2026-01-08 00:00'),(2,TIMESTAMP '2026-01-08 00:00')
), events(id,user_id,event_at,available_at,amount) AS (
VALUES (1,1,TIMESTAMP '2026-01-01',TIMESTAMP '2026-01-01',5),
(2,1,TIMESTAMP '2026-01-02',TIMESTAMP '2026-01-03',7),
(3,1,TIMESTAMP '2026-01-07',TIMESTAMP '2026-01-09',100),
(4,1,TIMESTAMP '2026-01-08',TIMESTAMP '2026-01-08',999)
)
SELECT p.user_id,COUNT(e.id) AS events_7d,COALESCE(SUM(e.amount),0) AS amount_7d
FROM predictions p LEFT JOIN events e ON e.user_id=p.user_id
AND e.event_at>=p.cutoff-INTERVAL '168 hours' AND e.event_at<p.cutoff
AND e.available_at<p.cutoff
GROUP BY p.user_id ORDER BY p.user_id;user_id | events_7d | amount_7d
1 | 2 | 12
2 | 0 | 0The January 7 event happened in the feature window but arrived January 9, so the model could not have used it on January 8. Filtering only event time leaks late information into a historical backtest. The cutoff event is also excluded. In a versioned source, reconstruct the row version available then, not today’s corrected version. Follow-up: how would you reproduce this dataset if historical availability timestamps were absent? The honest answer is that the original information set may be unidentifiable.
2. Compare conversion only for fully observed users
The observation window is the first 48 hours after a recorded exposure, with the endpoint excluded. Include only users whose entire window has elapsed before the fixed as-of timestamp. The output is by variant, not by exposure-date cohort. Each user has one authoritative exposure and variant in this fixture.
WITH params(as_of) AS (VALUES (TIMESTAMP '2026-10-17')),
exposures(user_id,variant,exposed_at) AS (
VALUES (1,'A',TIMESTAMP '2026-10-10'),(2,'A',TIMESTAMP '2026-10-10'),
(3,'B',TIMESTAMP '2026-10-10'),(4,'B',TIMESTAMP '2026-10-10'),
(5,'B',TIMESTAMP '2026-10-16')
), conversions(user_id,happened_at) AS (
VALUES (1,TIMESTAMP '2026-10-11'),(1,TIMESTAMP '2026-10-11 01:00'),
(3,TIMESTAMP '2026-10-09'),(3,TIMESTAMP '2026-10-11'),
(4,TIMESTAMP '2026-10-12')
), outcomes AS (
SELECT e.*,EXISTS(SELECT 1 FROM conversions c
WHERE c.user_id=e.user_id AND c.happened_at>=e.exposed_at
AND c.happened_at<e.exposed_at+INTERVAL '48 hours') AS converted
FROM exposures e CROSS JOIN params p
WHERE e.exposed_at+INTERVAL '48 hours'<=p.as_of
)
SELECT variant,COUNT(*) AS mature_users,
COUNT(*) FILTER(WHERE converted) AS converters,
ROUND(100.0*COUNT(*) FILTER(WHERE converted)/COUNT(*),2) AS rate_pct
FROM outcomes GROUP BY variant ORDER BY variant;variant | mature_users | converters | rate_pct
A | 2 | 1 | 50.00
B | 2 | 1 | 50.00Repeated conversions count once. A pre-exposure conversion does not suppress a later qualifying one. The conversion exactly 48 hours later fails, and user 5 is still immature. Source completeness must also cover the chosen cutoff. Equal observed rates in four users do not establish equivalence. A causal experiment additionally needs a valid assignment design, unit definition, exposure policy, and uncertainty analysis; this query alone cannot establish them.
3. Combine subgroup means without changing the weighting
WITH summaries(segment,total_value,observations) AS (
VALUES ('A',100::numeric,1),('B',0,9),('C',0,0)
)
SELECT ROUND(SUM(total_value)/NULLIF(SUM(observations),0),2) AS observation_weighted_mean,
ROUND(AVG(total_value/NULLIF(observations,0)),2) AS unweighted_segment_mean
FROM summaries;observation_weighted_mean | unweighted_segment_mean
10.00 | 50.00The first mean gives each of the ten observations equal weight. The second gives each of the two nonempty segments equal weight. Neither is a universal replacement for the other: choose the estimand. Segment C has no observations and contributes no segment mean. Before combining summaries, verify their counts refer to disjoint comparable observations, use the same missing-value policy, and have not already been rounded beyond acceptable precision.
4. Calculate a complete seven-calendar-day moving mean
The complete daily feed has activity only on January 1 and January 7, seven events each. A missing daily row means zero activity here. Create one row per date before applying a seven-row frame, and report only full seven-day frames.
WITH activity(day,events) AS (
VALUES (DATE '2026-01-01',7),(DATE '2026-01-07',7)
), calendar AS (
SELECT d::date AS day FROM generate_series(
TIMESTAMP '2026-01-01',TIMESTAMP '2026-01-07',INTERVAL '1 day') d
), daily AS (
SELECT c.day,COALESCE(a.events,0) AS events FROM calendar c LEFT JOIN activity a USING(day)
), rolling AS (
SELECT day,SUM(events) OVER w AS events_7d,COUNT(*) OVER w AS days_in_window
FROM daily WINDOW w AS (ORDER BY day ROWS BETWEEN 6 PRECEDING AND CURRENT ROW)
)
SELECT day,events_7d,ROUND(events_7d::numeric/days_in_window,2) AS daily_mean
FROM rolling WHERE days_in_window=7 ORDER BY day;day | events_7d | daily_mean
2026-01-07 | 14 | 2.00Averaging only the two observed rows gives 7, not the seven-day mean of 2. A ROWS frame is a calendar window only because the input has exactly one row per date. For several users, build the eligible user-date population and partition by user. For local days across daylight-saving changes, decide whether the feature is calendar-based or an elapsed-hour window.
Evaluate the answer beyond syntax
For each question, explain the population, time bounds, information availability, and denominator. Award yourself credit only when the expected output and the reason for every excluded row agree. Useful counterexamples include a late correction, a zero-size group, a repeated event, and a missing date caused by an ingestion outage rather than true inactivity.
Make the data assumptions testable
SQL can construct a reproducible analytical dataset, but it cannot infer absent provenance or certify causal conclusions. Keep event time, availability time, and reporting maturity distinct. Use metric clarification when the prompt leaves those choices open. Primary references: windows, EXISTS, and date series.
