HardWindow Functions High Frequency

Driver Earnings Quartile Segmentation with NTILE(4)

Frequently Asked By:AppleGoogleAmazon

Interactive In-Browser Workspace

Practice this problem on live schema tables in PostgreSQL 16 WASM with instant row verification and visual EXPLAIN plans.

Solve in Workbench

Problem Statement

Assign each driver in `driver_earnings` to an earnings quartile (1 to 4) using `NTILE(4)` ordered by `weekly_payout` descending. Return: 1. `driver_id` 2. `driver_name` 3. `weekly_payout` 4. `earnings_quartile`: `NTILE(4) OVER (ORDER BY weekly_payout DESC)` Order results by `weekly_payout` descending, then by `driver_id` ascending.

Why Technical Interviewers Ask This

Fleet operations segments active gig couriers into earnings quartiles to benchmark hourly incentive schemes and tier bonuses.

Common Pitfalls & Interview Traps

Forgetting required sorting and deterministic tie-breaking.

Not accounting for NULL or zero-value denominators during calculations.

Using inefficient quadratic subqueries instead of single-pass window functions constructs.

Canonical Modern SQL Solution

PostgreSQL 16 / ANSI SQL

SELECT 
        driver_id,
        driver_name,
        weekly_payout,
        NTILE(4) OVER (ORDER BY weekly_payout DESC) AS earnings_quartile
      FROM driver_earnings
      ORDER BY weekly_payout DESC, driver_id ASC;

Approach Breakdown

This query addresses the problem requirements by filtering and structuring data in Window Functions. It meets the target criteria: Drivers segmented into 4 earnings quartiles (1 = top 25%, 4 = bottom 25%).

Edge Cases to Verbally Clarify with Your Interviewer

The dataset contains zero matching records (must return valid empty result without crashing).

Multiple records tie on sorting criteria (must evaluate tie-breaking order deterministically).

Foreign keys or secondary attributes have NULL values.

Frequently Asked Follow-Up Questions

What makes this question a favorite among Apple and Google interviewers?

Interviewers use this problem to test whether candidates write defensive, scalable Window Functions queries that run efficiently on multi-million row production databases without excessive memory usage.

How does the query optimizer execute this query?

Using single-pass table scans with index lookups or sort buffers. Running EXPLAIN (ANALYZE, BUFFERS) in the DB Exercises workbench visualizes the execution nodes and buffer hit cache.