Quick summary
Summarize this blog with AI
A data analyst SQL take-home is not only a query-writing exercise. It tests whether you can turn unfamiliar files into a trustworthy decision without unlimited time, hidden manual steps, or claims the data cannot support. The strongest submission is a small, reproducible analysis whose assumptions, checks, and recommendation can survive a follow-up conversation.
The example assignment
Imagine a retailer provides four files and asks, Where should we place an additional 25,000 dollars of acquisition budget next quarter? The files are customers.csv, with signup timestamp and acquisition channel; orders.csv, with order timestamp, status, and order revenue; refunds.csv, with approved and rejected refund events; and daily_spend.csv, with marketing spend by date and channel. The extract cutoff is April 15, 2026.
The assignment allows SQL and one visualization and requests a short recommendation. It does not define attribution, cohort maturity, the refund window, or whether average historical return predicts marginal return. Those are analytical decisions, not reasons to stall.
Set scope and a timebox before opening the files
Convert the prompt into one primary decision and a few supporting metrics. Here, the decision is how to test allocation of incremental budget. The primary outcome is mature net revenue divided by aligned channel spend. Supporting metrics are acquired customers, activation rate, customer acquisition cost, gross revenue, and refund rate.
For a four-hour exercise, reserve roughly 30 minutes for profiling, 45 for staging and quality checks, 75 for analysis, 30 for validation, 30 for communication, and the remainder for documentation and a clean rerun. If time becomes tight, preserve reproducibility, core validation, and limitations before visual polish.
Profile files without changing the source
Keep supplied files untouched under data/raw. Record their names, sizes, and checksums so the input version is identifiable. For each file, inspect delimiter, quoting, encoding, headers, row count, and samples. Profile columns for blanks, candidate type, distinct count, range, and suspicious values. Load identifiers and dates as text initially so leading zeros survive and timestamp formats can be checked.
In the example, profiling finds that one customer_id begins with zero, blank acquisition channels exist, order_revenue has two malformed values, three duplicate refund event IDs are present, and spend dates end on February 28 even though order activity continues through April 15. Each finding affects either loading, scope, or interpretation.
Use a safe raw-to-typed staging flow
Create raw tables whose columns are text and load with the database client’s local import command. In PostgreSQL, \copy reads a local file through the client; server-side COPY may require broader filesystem privileges. Use an isolated schema and an explicit transaction for typed transformations. Do not build dynamic shell or SQL commands from file contents.
CREATE SCHEMA IF NOT EXISTS takehome;
CREATE TABLE takehome.raw_orders (
order_id text,
customer_id text,
ordered_at text,
status text,
order_revenue text
);
\copy takehome.raw_orders
FROM 'data/raw/orders.csv'
WITH (FORMAT csv, HEADER true, ENCODING 'UTF8');Raw text staging separates parsing failures from analytical logic. Before casting, query invalid patterns and log affected row counts. Never convert malformed revenue to zero: zero is a valid business value.
SELECT order_id, ordered_at
FROM takehome.raw_orders
WHERE NULLIF(TRIM(ordered_at), '') IS NULL
OR ordered_at !~
'^\d{4}-\d{2}-\d{2}[ T]\d{2}:\d{2}:\d{2}([+-]\d{2}:?\d{2}|Z)$';
SELECT order_id, order_revenue
FROM takehome.raw_orders
WHERE NULLIF(TRIM(order_revenue), '') IS NULL
OR order_revenue !~ '^-?[0-9]+(\.[0-9]{1,2})?$';A regular expression is only a first gate; the target type remains the authority. After reviewing exceptions, create typed tables with explicit casts. Preserve rejected rows with a reason, then rerun the full load in an empty schema to expose hidden dependencies.
Declare grain, keys, and relationships
Write one sentence for every table. One customer row represents one acquired customer. One order row represents one order. One refund row represents one refund event, so an order can have several. One spend row represents one channel on one calendar date. Candidate keys are customer_id, order_id, refund_id, and the pair spend_date, channel.
Test duplicate keys, orphaned foreign keys, timestamps outside the extract, negative spend, nonpositive refunds, and unexpected statuses. Exact duplicate refund events may be removed deterministically; conflicting duplicates need disclosure and an impact estimate. Normalize channel labels through one mapping table, keep unmapped values visible, and confirm that acquisition channel represents signup-time attribution.
Maintain a concise data-quality log
For each issue, log the check, affected row count, decision, and estimated impact.
Leading-zero customer identifier: one row; load identifiers as text; no information loss. Blank channel: 18 customers; map to unknown and exclude only from a channel-allocation recommendation, while retaining them in overall totals. Malformed order revenue: two orders; quarantine pending source clarification; maximum observed revenue exposure is disclosed. Repeated refund IDs: three exact duplicate rows; keep one per refund ID using a documented deduplication step. Spend coverage: spend ends February 28; restrict customer acquisition and spend comparison to the same period.
If quarantined orders could reverse the ranking, the recommendation is not robust. Otherwise, state the sensitivity result.
Choose a fair cohort and measurement window
Channels need equal opportunity to accumulate orders and refunds. The extract ends April 15. The example includes customers acquired from January 1 through February 28, matching available spend, and observes their orders during the first 30 elapsed days after signup. It attributes approved refunds against those orders through day 45. Every included customer therefore has the same follow-up period.
This avoids giving January customers more time to transact than later customers or dividing March acquisition by missing March spend. The metric is historical 45-day net return, not lifetime value or causal incrementality.
Build the analysis in auditable SQL layers
Assume the typed tables are stg_customers, stg_orders, stg_refunds, and stg_daily_spend. The following PostgreSQL query preserves customers with no orders, aggregates refunds before joining, and aligns spend to the acquisition dates.
WITH params AS (
SELECT
TIMESTAMPTZ '2026-01-01 00:00:00+00' AS cohort_start,
TIMESTAMPTZ '2026-03-01 00:00:00+00' AS cohort_end
),
mature_customers AS (
SELECT
c.customer_id,
c.signup_at,
c.acquisition_channel
FROM takehome.stg_customers AS c
CROSS JOIN params AS p
WHERE c.signup_at >= p.cohort_start
AND c.signup_at < p.cohort_end
),
eligible_orders AS (
SELECT
o.order_id,
o.customer_id,
o.ordered_at,
o.order_revenue
FROM takehome.stg_orders AS o
JOIN mature_customers AS c
ON c.customer_id = o.customer_id
WHERE o.status = 'completed'
AND o.ordered_at >= c.signup_at
AND o.ordered_at < c.signup_at + INTERVAL '30 days'
),
refund_by_order AS (
SELECT
e.order_id,
SUM(r.refund_amount) AS refund_amount
FROM eligible_orders AS e
JOIN mature_customers AS c
ON c.customer_id = e.customer_id
JOIN takehome.stg_refunds AS r
ON r.order_id = e.order_id
WHERE r.status = 'approved'
AND r.refunded_at >= e.ordered_at
AND r.refunded_at < c.signup_at + INTERVAL '45 days'
GROUP BY e.order_id
),
customer_value AS (
SELECT
c.customer_id,
c.acquisition_channel,
COUNT(e.order_id) AS completed_orders,
COALESCE(SUM(e.order_revenue), 0) AS gross_revenue,
COALESCE(SUM(r.refund_amount), 0) AS refunds
FROM mature_customers AS c
LEFT JOIN eligible_orders AS e
ON e.customer_id = c.customer_id
LEFT JOIN refund_by_order AS r
ON r.order_id = e.order_id
GROUP BY c.customer_id, c.acquisition_channel
),
channel_value AS (
SELECT
acquisition_channel,
COUNT(*) AS acquired_customers,
COUNT(*) FILTER (WHERE completed_orders > 0) AS activated_customers,
SUM(gross_revenue) AS gross_revenue,
SUM(refunds) AS refunds
FROM customer_value
GROUP BY acquisition_channel
),
channel_spend AS (
SELECT
s.acquisition_channel,
SUM(s.spend) AS spend
FROM takehome.stg_daily_spend AS s
CROSS JOIN params AS p
WHERE s.spend_date >= p.cohort_start::date
AND s.spend_date < p.cohort_end::date
GROUP BY s.acquisition_channel
)
SELECT
v.acquisition_channel,
v.acquired_customers,
v.activated_customers::numeric / NULLIF(v.acquired_customers, 0)
AS activation_rate,
s.spend / NULLIF(v.acquired_customers, 0) AS acquisition_cost,
v.gross_revenue,
v.refunds,
v.gross_revenue - v.refunds AS net_revenue_45d,
(v.gross_revenue - v.refunds) / NULLIF(s.spend, 0)
AS net_return_on_spend
FROM channel_value AS v
LEFT JOIN channel_spend AS s
ON s.acquisition_channel = v.acquisition_channel
ORDER BY net_return_on_spend DESC NULLS LAST;Refunds attach only to eligible orders before aggregation. NULLIF prevents division by zero, while a channel without spend remains visible with null efficiency metrics.
Validate before interpreting
Reconcile every grain. Mature customers must equal summed channel customers, eligible order IDs must be unique, activated customers cannot exceed acquired customers, and revenue, refunds, and aligned spend must reconcile to filtered sources.
Inspect edge cases explicitly: customers with no orders, orders at exactly day 30, refunds at exactly day 45, orders before signup, refunds before order time, fully refunded orders, refunds greater than order revenue, zero-spend channels, unknown channels, and channels present in only one file. Half-open windows exclude events exactly at the upper boundary, as documented.
Recalculate with day-30 and full-cutoff refunds and maximum plausible quarantined-order value. If the conclusion changes, make the recommendation conditional.
Turn results into a defensible recommendation
Suppose the validated output is as follows:
Channel Spend Customers Activation Net revenue Net return
Search 80,000 1,600 54% 120,000 1.50
Paid social 60,000 2,000 38% 72,000 1.20
Partnerships 20,000 400 61% 44,000 2.20Partnerships has the strongest historical return and activation but the smallest observed scale. Search has lower return but four times as many customers and a still-positive efficiency signal. Paid social acquires customers cheaply, yet fewer activate and its mature net return is weakest.
Allocate 15,000 dollars to a controlled Search expansion and 10,000 dollars to a Partnerships scale test. Precommit to stop or expand criteria: net return above 1.35 after a mature cohort, refund rate within three percentage points of baseline, and adequate sample size. Historical averages do not guarantee marginal returns.
Customer mix, targeting, seasonality, and promotions may confound the comparison. Treat the allocation as a measured experiment, not a causal forecast.
Select one visualization that supports the decision
Use a horizontal dot plot of net return by channel with direct labels, a threshold reference line, and cohort-size annotations. It shows the ranking and Partnerships' small sample without a misleading second axis. Label the metric and dates precisely; add uncertainty intervals if available.
Package the submission for a clean rerun
Use a README, untouched permitted inputs under data/raw, numbered SQL files for load through validation, results under output, and the chart under figures. If inputs cannot be redistributed, provide expected names and checksums only.
Document the question, recommendation, environment, run order, metrics, assumptions, quality decisions, validations, limitations, and file guide. Remove credentials and personal paths. Verify that an empty schema can regenerate every reported result.
Handle AI and tool policies explicitly
Follow the stated tool policy. If AI is prohibited, do not use it. Otherwise, never share confidential data without permission and verify every generated query, calculation, and claim. Disclose the tool and its limited role when required, and submit only work you can explain.
Prepare to defend the choices
Expect to explain the February cohort end, 45-day refunds, deduplication, why return is not profit, and what would change the recommendation. Be ready to extend the refund window in one common table expression and note that gross margin would be preferable if cost were available. Choices need not be universal; they must be explicit, tested, and revisable.
FAQ
How much analysis is enough for a take-home?
Enough to answer the decision with validated evidence and material limitations. Two well-defined metrics and one useful sensitivity check are stronger than many loosely related cuts. Stop when additional work is unlikely to change or qualify the recommendation.
Should I clean bad rows or exclude them?
Use a rule tied to evidence. Normalize harmless formatting, deterministically deduplicate proven repeated events, and quarantine values that cannot be safely interpreted. Report counts and test whether exclusions could change the conclusion. Never make silent repairs.
Why not include every customer through the extract date?
Recent customers have less time to order and refund, which biases channel comparisons when acquisition timing differs. A mature cohort gives each customer equal follow-up. An alternative is survival or cohort-age analysis, but it adds complexity that may not be needed.
What if one channel has no spend data?
Keep it in customer and revenue summaries, but leave cost and return metrics null. It may be organic, mislabeled, or missing data. Do not compare it with paid channels until the denominator is understood.
Should the recommendation allocate the full budget precisely?
Only as precisely as the evidence permits. A test allocation with guardrails is often more credible than claiming an exact optimum from observational averages. Describe what outcome would cause the business to expand, stop, or redirect the test.
What belongs in the final presentation?
Lead with the recommendation and its decision-relevant evidence. Then show the cohort definition, one clear visual, material data-quality choices, and limitations. Put detailed queries in the submission files so the narrative remains concise without sacrificing auditability.
