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.
We'll cover the following...
- What makes North’s regional report correct?
- What does one customer, order or update row represent?
- Which complete update wins for O1 and O2?
- How do South and missing histories survive the joins?
- How do the two North rows become one regional result?
- How can the same three regions carry totals and rankings?
- Which plausible repairs still answer the wrong question?
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, exclude2026-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
NULLshare.
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);
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.