Search⌘ K
AI Features

The SQL Question

Solve the SQL question in the Palantir FDSE screen by fixing row grain before you aggregate. Spot join fan-out that inflates counts and sums, select the latest complete history row per entity using ROW_NUMBER with a deterministic tie-breaker, and place LEFT JOIN filters correctly so zero-order groups survive. Handle NULLs and zero denominators safely, use window functions for totals and rankings, and predict exact results before you run a query.

North has two quarter orders, yet a delivery report counts six. The SQL runs successfully; joining each order to its delivery history has multiplied the facts being counted. A correct regional report requires explicit decisions about what each row represents, which history record wins, and which customers must remain visible. Establish the required row grain before calculating metrics.

What makes North’s regional report correct?

Suppose the business asks for each customer region’s Q3 2026 order count, late-delivered order count, unknown-delivery count, loss cents attributable to late-delivered orders, and late share of all quarter orders.

Agree on these rules before writing SQL:

  • Quarter membership follows order placement: Include 2026-07-01T00:00:00Z, exclude 2026-10-01T00:00:00Z.

  • Region comes from the supplied customer table. Preserve every region represented by customers, including regions with no quarter orders.

  • Delivery classification uses the latest eligible update for each order.

  • Loss is an order-level amount, counted once when that order is late.

  • Late share divides late orders by all quarter orders. A zero-order region has a NULL share.

Public preparation compilations describe SQL joins and aggregation as possible assessment content. The evidence establishes neither a universal SQL assessment nor guaranteed window-function questions. Confirm your actual format. One preparation guide reports an orders/customers/deliveries theme involving regional losses; the specific schema, records and metric definitions here are course-authored preparation.

What does one customer, order or update row represent?

A table’s grain states what one row represents. A primary key uniquely identifies a row; a foreign key relates it to a row in another table. Relationship cardinality,one-to-many here,predicts how many combinations a join can produce.

The PostgreSQL 16 schema is:

CREATE TABLE customers (customer_id text PRIMARY KEY, name text NOT NULL, region text NOT NULL);
CREATE TABLE orders (order_id text PRIMARY KEY, customer_id text NOT NULL REFERENCES customers(customer_id), placed_at timestamptz NOT NULL, promised_at timestamptz NOT NULL, loss_cents bigint NOT NULL CHECK (loss_cents >= 0));
CREATE TABLE delivery_updates (update_id bigint PRIMARY KEY, order_id text NOT NULL REFERENCES orders(order_id), event_at timestamptz, status text NOT NULL, delivered_at timestamptz);
Create customers, orders, and delivery update tables

These three statements create one row per customer, order and delivery update respectively. Customer regions, order timestamps and nonnegative order losses are required; event and delivery timestamps may be missing.

The worked fixture has customers C1/Alex/North, C2/Alex/East and C3/Robin/South. The repeated name Alex shows why names cannot substitute for customer IDs.

Open the complete reference file for the inserts and queries. Local execution is optional; use a disposable PostgreSQL 16 database because each file resets these three tables.

sql
DROP TABLE IF EXISTS delivery_updates, orders, customers;
SET TIME ZONE 'UTC';
CREATE TABLE customers (customer_id text PRIMARY KEY, name text NOT NULL, region text NOT NULL);
CREATE TABLE orders (order_id text PRIMARY KEY, customer_id text NOT NULL REFERENCES customers(customer_id), placed_at timestamptz NOT NULL, promised_at timestamptz NOT NULL, loss_cents bigint NOT NULL CHECK (loss_cents >= 0));
CREATE TABLE delivery_updates (update_id bigint PRIMARY KEY, order_id text NOT NULL REFERENCES orders(order_id), event_at timestamptz, status text NOT NULL, delivered_at timestamptz);
-- Complete reference fixture; no edits are required.
-- Optional execution only: use a disposable PostgreSQL 16 database.
-- DROP TABLE above resets only these three lesson tables, not the database.
-- Assumes PostgreSQL 16 and psql are already available; no installation
-- is required to inspect this file or work through the lesson in prose.
-- Optional command:
-- psql -X -v ON_ERROR_STOP=1 -d YOUR_DISPOSABLE_DATABASE -f delivery_report.sql
-- All results described below are expectations, not observed execution.
INSERT INTO customers (customer_id, name, region) VALUES
('C1','Alex','North'),
('C2','Alex','East'),
('C3','Robin','South');
INSERT INTO orders (order_id, customer_id, placed_at, promised_at, loss_cents) VALUES
('O1','C1','2026-07-01T09:00:00Z','2026-07-02T12:00:00Z',100),
('O2','C1','2026-08-01T09:00:00Z','2026-08-02T12:00:00Z',100),
('O3','C2','2026-09-01T09:00:00Z','2026-09-02T12:00:00Z',50),
('O4','C1','2026-10-01T00:00:00Z','2026-10-02T12:00:00Z',900);
INSERT INTO delivery_updates (update_id, order_id, event_at, status, delivered_at) VALUES
(10,'O1','2026-07-01T10:00:00Z','in_transit',NULL),
(11,'O1','2026-07-03T10:00:00Z','delivered','2026-07-02T11:00:00Z'),
(12,'O1','2026-07-03T10:00:00Z','delivered','2026-07-02T13:00:00Z'),
(13,'O2','2026-08-01T10:00:00Z','in_transit',NULL),
(14,'O2','2026-08-02T12:00:00Z','delivered','2026-08-02T11:00:00Z'),
(15,'O2',NULL,'delivered','2026-08-02T13:00:00Z');
-- INCORRECT REPORT GRAIN: direct North Q3 history join.
-- Expected: six rows, not two order rows. Summing their loss_cents gives
-- 600 instead of the order-level total 200; counting rows gives six.
SELECT o.order_id, o.loss_cents, du.update_id, du.event_at,
du.status, du.delivered_at
FROM customers AS c
JOIN orders AS o ON o.customer_id = c.customer_id
JOIN delivery_updates AS du ON du.order_id = o.order_id
WHERE c.region = 'North'
AND o.placed_at >= TIMESTAMPTZ '2026-07-01T00:00:00Z'
AND o.placed_at < TIMESTAMPTZ '2026-10-01T00:00:00Z'
ORDER BY o.order_id, du.update_id;
-- DIAGNOSTIC: eligible latest complete rows, before regional aggregation.
-- Expected: O1/update 12 (delivered_at 2026-07-02T13:00:00Z),
-- O2/update 14 (delivered_at 2026-08-02T11:00:00Z).
-- Update 15 is ineligible because event_at is NULL.
WITH ranked_updates AS (
SELECT du.*,
ROW_NUMBER() OVER (
PARTITION BY order_id
ORDER BY event_at DESC, update_id DESC
) AS rn
FROM delivery_updates AS du
WHERE event_at IS NOT NULL
), latest_updates AS (
SELECT update_id, order_id, event_at, status, delivered_at
FROM ranked_updates
WHERE rn = 1
)
SELECT update_id, order_id, event_at, status, delivered_at
FROM latest_updates
ORDER BY order_id;
-- REFERENCE REPORT: one row per customer region, including zero-order regions.
WITH ranked_updates AS (
SELECT du.*,
ROW_NUMBER() OVER (
PARTITION BY order_id
ORDER BY event_at DESC, update_id DESC
) AS rn
FROM delivery_updates AS du
WHERE event_at IS NOT NULL
), latest_updates AS (
SELECT update_id, order_id, event_at, status, delivered_at
FROM ranked_updates
WHERE rn = 1
), regional_metrics AS (
SELECT c.region,
COUNT(o.order_id) AS order_count,
SUM(CASE WHEN lu.status = 'delivered'
AND lu.delivered_at > o.promised_at
THEN 1 ELSE 0 END) AS late_count,
SUM(CASE WHEN o.order_id IS NOT NULL
AND (lu.update_id IS NULL
OR lu.status <> 'delivered'
OR lu.delivered_at IS NULL)
THEN 1 ELSE 0 END) AS unknown_count,
SUM(CASE WHEN lu.status = 'delivered'
AND lu.delivered_at > o.promised_at
THEN o.loss_cents ELSE 0 END) AS late_loss_cents
FROM customers AS c
LEFT JOIN orders AS o
ON o.customer_id = c.customer_id
AND o.placed_at >= TIMESTAMPTZ '2026-07-01T00:00:00Z'
AND o.placed_at < TIMESTAMPTZ '2026-10-01T00:00:00Z'
LEFT JOIN latest_updates AS lu ON lu.order_id = o.order_id
GROUP BY c.region
)
SELECT region, order_count, late_count, unknown_count, late_loss_cents,
late_count::numeric / NULLIF(order_count, 0) AS late_share
FROM regional_metrics
ORDER BY region;
-- Expected values (numeric display may include trailing zeros;
-- psql normally displays NULL as an empty field):
-- region order_count late_count unknown_count late_loss_cents late_share
-- East 1 0 1 0 0
-- North 2 1 0 100 0.5
-- South 0 0 0 0 NULL
-- WINDOW ANNOTATION: repeat the CTEs so this statement is independently scoped.
WITH ranked_updates AS (
SELECT du.*,
ROW_NUMBER() OVER (
PARTITION BY order_id
ORDER BY event_at DESC, update_id DESC
) AS rn
FROM delivery_updates AS du
WHERE event_at IS NOT NULL
), latest_updates AS (
SELECT update_id, order_id, event_at, status, delivered_at
FROM ranked_updates
WHERE rn = 1
), regional_metrics AS (
SELECT c.region,
COUNT(o.order_id) AS order_count,
SUM(CASE WHEN lu.status = 'delivered'
AND lu.delivered_at > o.promised_at
THEN 1 ELSE 0 END) AS late_count,
SUM(CASE WHEN o.order_id IS NOT NULL
AND (lu.update_id IS NULL
OR lu.status <> 'delivered'
OR lu.delivered_at IS NULL)
THEN 1 ELSE 0 END) AS unknown_count,
SUM(CASE WHEN lu.status = 'delivered'
AND lu.delivered_at > o.promised_at
THEN o.loss_cents ELSE 0 END) AS late_loss_cents
FROM customers AS c
LEFT JOIN orders AS o
ON o.customer_id = c.customer_id
AND o.placed_at >= TIMESTAMPTZ '2026-07-01T00:00:00Z'
AND o.placed_at < TIMESTAMPTZ '2026-10-01T00:00:00Z'
LEFT JOIN latest_updates AS lu ON lu.order_id = o.order_id
GROUP BY c.region
)
SELECT region, late_loss_cents,
SUM(late_loss_cents) OVER () AS total_late_loss_cents,
RANK() OVER (ORDER BY late_loss_cents DESC) AS loss_rank,
DENSE_RANK() OVER (ORDER BY late_loss_cents DESC) AS dense_loss_rank,
ROW_NUMBER() OVER (ORDER BY late_loss_cents DESC, region) AS position,
SUM(late_loss_cents) OVER (
ORDER BY late_loss_cents DESC, region
ROWS BETWEEN UNBOUNDED PRECEDING AND CURRENT ROW
) AS cumulative_late_loss_cents
FROM regional_metrics
ORDER BY late_loss_cents DESC, region;
-- Expected values, not observed output:
-- region | late_loss_cents | total_late_loss_cents | loss_rank | dense_loss_rank | position | cumulative_late_loss_cents
-- North | 100 | 100 | 1 | 1 | 1 | 100
-- East | 0 | 100 | 2 | 2 | 2 | 100
-- South | 0 | 100 | 2 | 2 | 3 | 100
...