Interview Prep
Problems drawn from real interviews at product companies. Solve them in JavaScript, TypeScript, Python or SQL.
Difficulty
Topics
Language
36 challenges
Products store their variable attributes as a JSON blob in a single TEXT column, rather than one column per possible attribute. Table: products | id | name | attrs | |----|--------|-------------------------------------| | 1 | Widget | {"color":"red","price":19.99} | | 2 | Gadget | {"color":"blue","price":29.99} | Write a query that returns each product's id, name, and the color pulled out of attrs. Expected output (columns: id, name, color), ordered by id: > Hint — SQLite's JSON functions use $.field path syntax: > json_extract(attrs, '$.color'). (SQLite 3.27, which this platform runs, > predates the ->/->> operator syntax — that needs 3.38+ — so > json_extract() is the only option here.)
Your company stores VIP customers and standard customers in separate tables. Write a SQL query that returns all unique emails from both tables, sorted alphabetically. Tables vip_customers: id, email standard_customers: id, email | vip | email | |-----|--------------------| | 1 | alice@example.com | | 2 | bob@example.com | | 3 | carol@example.com | | standard | email | |----------|--------------------| | 1 | dave@example.com | | 2 | alice@example.com | | 3 | eve@example.com | Expected output (column: email) — alice appears once despite being in both tables. > UNION removes duplicates. Use UNION ALL to keep them.
Write a SQL query that returns the name and revenue of the 3 best-selling products, ordered by revenue descending. Table: products | id | name | revenue | category | |----|---------------|---------|-------------| | 1 | Laptop Pro | 45000 | Electronics | | 2 | Coffee Maker | 12000 | Appliances | | 3 | Wireless Mouse| 8000 | Electronics | | 4 | Standing Desk | 28000 | Furniture | | 5 | Headphones | 18000 | Electronics | | 6 | Office Chair | 22000 | Furniture | | 7 | Webcam | 6000 | Electronics | Expected output (columns: name, revenue) > LIMIT 3 returns at most 3 rows from the result.
Write a SQL query that returns, for each subject, the number of students, the lowest score, and the highest score. Order the results by subject name. Table: exam_scores | id | student | subject | score | |----|---------|---------|-------| | 1 | Alice | Math | 75 | | 2 | Bob | Math | 85 | | 3 | Carol | Science | 60 | | 4 | Dave | Science | 90 | | 5 | Eve | Math | 92 | | 6 | Frank | Science | 78 | Expected output (columns: subject, students, lowest, highest)
Write a SQL query that returns the title and author of all books whose title contains the word "Data" (case-insensitive), ordered by title. Table: books | id | title | author | year | |----|------------------------|---------|------| | 1 | Data Science Handbook | Smith | 2020 | | 2 | Machine Learning Intro | Jones | 2019 | | 3 | Database Design Basics | Brown | 2021 | | 4 | Data Engineering Guide | Davis | 2022 | | 5 | Clean Code | Martin | 2008 | | 6 | Big Data Analytics | Wilson | 2023 | Expected output (columns: title, author) > LIKE '%Data%' matches any title containing "Data" anywhere. > SQLite's LIKE is case-insensitive for ASCII letters.
Write a SQL query that returns the distinct categories found in the products table, sorted alphabetically. Table: products | id | name | price | category | |----|----------------|-------|-------------| | 1 | Laptop | 999 | Electronics | | 2 | Wireless Mouse | 29 | Electronics | | 3 | Standing Desk | 349 | Furniture | | 4 | Office Chair | 199 | Furniture | | 5 | Notebook | 5 | Stationery | | 6 | Pen Set | 8 | Stationery | | 7 | Headphones | 79 | Electronics | Expected output (column: category) > Use SELECT DISTINCT to eliminate duplicate category values.
Write a SQL query that returns all flights with a price between $80 and $250 inclusive, showing the route and price, ordered by price ascending. Table: flights | id | route | price | airline | |----|----------|-------|-----------| | 1 | NYC-LAX | 250 | United | | 2 | NYC-CHI | 120 | Delta | | 3 | LAX-SFO | 80 | Southwest | | 4 | CHI-MIA | 320 | American | | 5 | SFO-SEA | 90 | Alaska | | 6 | NYC-BOS | 55 | JetBlue | Expected output (columns: route, price) > BETWEEN a AND b is inclusive on both ends — equivalent to >= a AND <= b.
Write a SQL query that returns each employee's name alongside their department name, ordered by employee name. Tables departments: id, name employees: id, name, dept_id | dept | name | |------|-------------| | 1 | Engineering | | 2 | Marketing | | 3 | Sales | | emp | name | dept_id | |-----|-------|---------| | 1 | Alice | 1 | | 2 | Bob | 2 | | 3 | Carol | 1 | | 4 | Dave | 3 | Expected output (columns: employee_name, department_name) > SQLite output format — rows are pipe-separated (|) with no column headers. > Example: SELECT id, name FROM users WHERE id = 1 → 1|Alice
Write a SQL query that returns all employees in the Engineering department, ordered by salary descending. Table: employees | id | name | salary | department | |----|-------|--------|-------------| | 1 | Alice | 75000 | Engineering | | 2 | Bob | 45000 | Marketing | | 3 | Carol | 82000 | Engineering | | 4 | Dave | 55000 | Marketing | | 5 | Eve | 91000 | Engineering | Expected output (columns: id, name, salary) > SQLite output format — rows are pipe-separated (|) with no column headers. > Example: SELECT id, name FROM users WHERE id = 1 → 1|Alice
Write a SQL query that counts the number of orders per status, ordered alphabetically by status. Table: orders | id | customer | amount | status | |----|----------|--------|-----------| | 1 | Alice | 120 | completed | | 2 | Bob | 80 | completed | | 3 | Alice | 200 | completed | | 4 | Carol | 150 | pending | | 5 | Bob | 90 | pending | Expected output (columns: status, count) > SQLite output format — rows are pipe-separated (|) with no column headers. > Example: SELECT id, name FROM users WHERE id = 1 → 1|Alice
Table: daily_sales | day | amount | |-----|--------| | 1 | 10 | | 2 | 20 | | 3 | 30 | | 4 | 40 | | 5 | 50 | Write a query that returns each day's amount alongside a moving average over that day and the two preceding days (or fewer, near the start). Expected output (columns: day, amount, moving_avg), ordered by day: > Hint — unlike the running-total window functions elsewhere in this > catalog (OVER (ORDER BY ...) with an implicit unbounded frame), a moving > average needs an explicit, bounded frame: ROWS BETWEEN 2 PRECEDING > AND CURRENT ROW.
Table: page_views (page is the primary key) Recording a page view should increment the existing counter if the page has been seen before, or insert a new row starting at 1 if it hasn't — in a single atomic statement, not a separate "check then insert-or-update" round trip. Write a statement that records one view for '/home', then select the final state of the table. Expected output (columns: page, views), ordered by page: If /home already had 10 views → /home|11 If /home didn't exist yet (only some other page did) → that other page unchanged, plus a new /home|1 row > Hint — INSERT ... ON CONFLICT(page) DO UPDATE SET views = views + 1 > is a single statement: SQLite attempts the insert, and only runs the > DO UPDATE clause if it collides with an existing primary key.
An e-commerce catalog stores categories in a self-referencing tree. Write a query that returns every category alongside the id of its top-level root ancestor (a root category is its own root). Table: categories | id | name | parent_id | |----|-------------|-----------| | 1 | Electronics | NULL | | 2 | Computers | 1 | | 3 | Laptops | 2 | | 4 | Clothing | NULL | | 5 | Shoes | 4 | Expected output (columns: id, name, root_id), ordered by id: > Hint — this is the mirror image of the ancestry-path challenge: instead > of accumulating a growing path, carry the root's own id unchanged all > the way down through every recursive step.
Write a query that returns every employee alongside their depth level in the org chart: the CEO (no manager) is level 0, their direct reports are level 1, and so on. Table: employees | id | name | manager_id | |----|-------------|------------| | 1 | CEO | NULL | | 2 | VP Eng | 1 | | 3 | VP Sales | 1 | | 4 | Eng Manager | 2 | | 5 | Engineer A | 4 | | 6 | Engineer B | 4 | | 7 | Sales Rep | 3 | Expected output (columns: id, name, level), ordered by id: > Hint — use WITH RECURSIVE: the base case is the row(s) with > manager_id IS NULL at level 0, the recursive case joins employees > back onto the CTE through manager_id and adds 1 to the level.
Write a query that returns every employee's full path from the top of the org chart down to them, as a single string joined with > . Table: employees | id | name | manager_id | |----|-------------|------------| | 1 | CEO | NULL | | 2 | VP Eng | 1 | | 3 | VP Sales | 1 | | 4 | Eng Manager | 2 | | 5 | Engineer A | 4 | | 6 | Engineer B | 4 | | 7 | Sales Rep | 3 | Expected output (columns: id, path), ordered by id: > Hint — build the path by string-concatenating (||) the parent's > already-built path with > and the current employee's name.
Table: users | id | name | profile | |----|-------|---------------------------------------------------| | 1 | Alice | {"address":{"city":"Paris","zip":"75001"}} | | 2 | Bob | {"address":{"city":"Lyon","zip":"69001"}} | | 3 | Carol | {"address":{"city":"Paris","zip":"75002"}} | Write a query that returns the id and name of every user whose profile.address.city is 'Paris'. Expected output (columns: id, name), ordered by id: > Hint — json_extract accepts multi-level paths directly: > json_extract(profile, '$.address.city') reaches two levels deep in one > call. You can filter on the extracted value in a normal WHERE clause.
This is the reverse of exploding a JSON array: given normal relational rows, build one JSON array per group. Table: order_items | order_id | product | |----------|---------| | 1 | apple | | 1 | banana | | 2 | milk | Write a query that returns each order_id alongside a JSON array of all its products. Expected output (columns: order_id, products), ordered by order_id: > Hint — json_group_array(expr) is an aggregate function, just like > SUM or COUNT — use it with GROUP BY.
Orders store their line items as a JSON array of product names in a single column, rather than a separate line-items table. Table: orders | id | items | |----|----------------------------------| | 1 | ["apple","banana","cherry"] | | 2 | ["milk","bread"] | Write a query that returns one row per item: the order's id next to each individual item from its items array. Expected output (columns: id, item), ordered by id then array position: > Hint — json_each(column) is a table-valued function: put it in > the FROM clause (comma-joined with the outer table) and it produces one > row per array element, with .value and .key (the array index) columns.
Write a SQL query that returns each order's customer name, product name, quantity, and total cost (quantity × price), by joining three tables. Order by customer name, then product name. Tables | customers | id | name | city | |-----------|----|-------|------| | | 1 | Alice | NYC | | | 2 | Bob | LA | | | 3 | Carol | NYC | | products | id | name | price | |-----------|----|----------|-------| | | 1 | Laptop | 999 | | | 2 | Mouse | 29 | | | 3 | Keyboard | 79 | | orders | id | customer_id | product_id | quantity | |--------|----|-------------|------------|----------| | | 1 | 1 | 1 | 1 | | | 2 | 1 | 2 | 2 | | | 3 | 2 | 3 | 1 | | | 4 | 3 | 1 | 2 | Expected output (columns: customer, product, qty, total)
The staff table stores every person including their manager (via manager_id). The CEO has manager_id = NULL. Write a SQL query that returns each non-CEO employee's name alongside their manager's name, ordered by employee name. Table: staff | id | name | manager_id | |----|---------|------------| | 1 | CEO | NULL | | 2 | Alice | 1 | | 3 | Bob | 1 | | 4 | Carol | 2 | | 5 | Dave | 2 | | 6 | Eve | 3 | Expected output (columns: employee, manager) > A self-join joins a table to itself using two aliases to treat rows as > different "roles" (employee vs manager).
Write a SQL query that returns the names of all customers who have placed at least one order, ordered alphabetically. Tables customers: id, name orders: id, customer_id, amount | customers | | orders | | | |-----------|-------|-----------|-------------|--------| | id | name | id | customer_id | amount | | 1 | Alice | 1 | 1 | 150 | | 2 | Bob | 2 | 1 | 200 | | 3 | Carol | 3 | 3 | 75 | | 4 | Dave | | | | Bob and Dave have no orders → excluded. Expected output (column: name) > EXISTS (subquery) returns TRUE if the subquery yields any row. > It stops scanning as soon as one match is found — efficient for large tables.
Write a SQL query that returns all users who subscribed in the year 2024, showing user_name, plan, and start_date, ordered by start_date. Table: subscriptions | id | user_name | plan | start_date | |----|-----------|-------|------------| | 1 | Alice | Pro | 2023-11-15 | | 2 | Bob | Free | 2024-02-20 | | 3 | Carol | Pro | 2024-06-01 | | 4 | Dave | Teams | 2023-08-10 | | 5 | Eve | Free | 2024-09-30 | | 6 | Frank | Pro | 2023-12-01 | Expected output > In SQLite, strftime('%Y', date_column) extracts the 4-digit year as a string.
Write a SQL query using a correlated subquery to find employees whose salary is strictly above the average salary of their own department. Return name, department, salary, ordered by department then salary descending. Table: employees | id | name | department | salary | |----|-------|-------------|--------| | 1 | Alice | Engineering | 95000 | | 2 | Bob | Engineering | 72000 | | 3 | Carol | Engineering | 85000 | | 4 | Dave | Marketing | 55000 | | 5 | Eve | Marketing | 62000 | | 6 | Frank | Marketing | 48000 | Engineering avg = 84 000 → Alice (95k ✓), Carol (85k ✓), Bob (72k ✗) Marketing avg = 55 000 → Eve (62k ✓), Dave (55k = avg ✗), Frank (48k ✗) Expected output
A contacts table stores a phone and a mobile number, either of which may be NULL. Write a SQL query that returns each contact's name and their best available number: phone first, then mobile, then 'N/A' if both are NULL. Order by name. Table: contacts | id | name | phone | mobile | |----|-------|----------|----------| | 1 | Alice | 555-1001 | NULL | | 2 | Bob | NULL | 555-2002 | | 3 | Carol | 555-3003 | 555-3004 | | 4 | Dave | NULL | NULL | | 5 | Eve | 555-5005 | NULL | Expected output (columns: name, contact_number) > COALESCE(a, b, c) returns the first non-NULL argument.
Write a SQL query that adds a band column to each employee row: 'Senior' — salary ≥ 90 000 'Mid' — salary 50 000 – 89 999 'Junior' — salary < 50 000 Return name, salary, band, ordered by salary descending. Table: employees | id | name | department | salary | |----|-------|-------------|--------| | 1 | Alice | Engineering | 95000 | | 2 | Bob | Marketing | 52000 | | 3 | Carol | Engineering | 68000 | | 4 | Dave | Sales | 38000 | | 5 | Eve | Engineering | 120000 | | 6 | Frank | Marketing | 45000 | Expected output
Write a SQL query that finds all customers whose total order amount exceeds $200, ordered by total amount descending. Table: orders | id | customer | amount | |----|----------|--------| | 1 | Alice | 120 | | 2 | Bob | 80 | | 3 | Alice | 200 | | 4 | Carol | 150 | | 5 | Bob | 90 | | 6 | Alice | 75 | | 7 | Carol | 60 | Alice total = 395, Carol total = 210, Bob total = 170. Expected output (columns: customer, total) > SQLite output format — rows are pipe-separated (|) with no column headers. > Example: SELECT id, name FROM users WHERE id = 1 → 1|Alice
Write a SQL query that finds all departments that have no employees, ordered by department name. Tables departments: id, name employees: id, name, dept_id | dept | name | |------|-------------| | 1 | Engineering | | 2 | Marketing | | 3 | Legal | | 4 | Sales | Employees exist in Engineering (1), Marketing (2) and Sales (4) — but not Legal (3). Expected output (column: department_name) > SQLite output format — rows are pipe-separated (|) with no column headers. > Example: SELECT id, name FROM users WHERE id = 1 → 1|Alice > Hint — use a LEFT JOIN and check for IS NULL on the employee side.
Table: deals | rep | deal_date | amount | |-------|------------|--------| | Alice | 2024-01-01 | 100 | | Alice | 2024-02-01 | 300 | | Alice | 2024-03-01 | 200 | | Bob | 2024-01-15 | 50 | | Bob | 2024-02-15 | 80 | Write a query that returns every deal alongside that rep's first deal amount and last deal amount (chronologically), as two extra columns on every row. Expected output (columns: rep, deal_date, amount, first_deal, last_deal), ordered by rep then deal_date: > Hint — FIRST_VALUE/LAST_VALUE need PARTITION BY rep so each > rep's deals are considered independently. LAST_VALUE additionally needs > an explicit ROWS BETWEEN UNBOUNDED PRECEDING AND UNBOUNDED FOLLOWING > frame — otherwise it (confusingly) only sees up to the current row, and > "last" would change on every row instead of being the partition's actual > last deal.
Write a query that returns, for every manager, the total number of people under them — direct reports and indirect reports (their reports' reports, and so on). Employees with nobody under them should not appear at all. Table: employees | id | name | manager_id | |----|-------------|------------| | 1 | CEO | NULL | | 2 | VP Eng | 1 | | 3 | VP Sales | 1 | | 4 | Eng Manager | 2 | | 5 | Engineer A | 4 | | 6 | Engineer B | 4 | | 7 | Sales Rep | 3 | Expected output (columns: ancestor_id, total_reports), ordered by ancestor_id: > Hint — first build every (ancestor_id, descendant_id) pair the > hierarchy implies (a self-join-like recursive CTE), then GROUP BY > the ancestor to count.
Write a SQL query that shows, for each employee, their name, department, salary, and what percentage of their department's total salary budget they represent — rounded to 1 decimal place. Order the output by department, then by salary descending. Table: employees | id | name | department | salary | |----|-------|-------------|--------| | 1 | Alice | Engineering | 60000 | | 2 | Bob | Engineering | 40000 | | 3 | Dave | Marketing | 75000 | | 4 | Eve | Marketing | 50000 | | 5 | Frank | Marketing | 25000 | Engineering total = 100 000 → Alice 60 %, Bob 40 % Marketing total = 150 000 → Dave 50 %, Eve 33.3 %, Frank 16.7 % Expected output (columns: name, department, salary, pct) > Combine SUM(salary) OVER (PARTITION BY department) with regular > arithmetic — this is the pattern that separates intermediate from > advanced SQL practitioners.
SQLite has no built-in MEDIAN() function. Write a SQL query that computes the median salary from the employees table using window functions. The median is the middle value when rows are sorted: Odd count → the single middle row. Even count → the average of the two middle rows. Table: employees | id | name | salary | |----|-------|--------| | 1 | Alice | 40000 | | 2 | Bob | 55000 | | 3 | Carol | 70000 | | 4 | Dave | 85000 | | 5 | Eve | 95000 | Sorted: 40000 · 55000 · 70000 · 85000 · 95000 → median = 70000 Expected output (column: median) > Key insight — assign each row a rank with ROW_NUMBER() OVER (ORDER BY salary) > and compute the total row count with COUNT(*) OVER (). > For a table with N rows, the median row(s) are at positions > (N+1)/2 and (N+2)/2 (integer division). Taking AVG of those > row(s) handles both odd and even N.
Write a SQL query that returns the top 2 highest-paid employees in each department, ordered by department name then salary descending. Table: employees | id | name | salary | dept | |----|-------|--------|-------------| | 1 | Alice | 90000 | Engineering | | 2 | Bob | 75000 | Engineering | | 3 | Carol | 60000 | Engineering | | 4 | Dave | 80000 | Marketing | | 5 | Eve | 70000 | Marketing | | 6 | Frank | 65000 | Marketing | Expected output (columns: name, dept, salary) > Hint — assign a row number per department with > ROW_NUMBER() OVER (PARTITION BY dept ORDER BY salary DESC), then filter > rows where that number ≤ 2.
Write a SQL query that returns each month alongside its sales amount and the cumulative (running) total up to that month, ordered chronologically. Table: sales | id | month | amount | |----|---------|--------| | 1 | 2024-01 | 100 | | 2 | 2024-02 | 150 | | 3 | 2024-03 | 200 | | 4 | 2024-04 | 120 | Expected output (columns: month, amount, running_total) > Hint — use SUM(amount) OVER (ORDER BY month) as a window function.
Write a SQL query that shows, for each month, the revenue and the previous month's revenue side by side. Use 0 as the default when there is no previous month. Order by month. Table: revenue | id | month | amount | |----|---------|--------| | 1 | 2024-01 | 200 | | 2 | 2024-02 | 350 | | 3 | 2024-03 | 300 | | 4 | 2024-04 | 450 | Expected output (columns: month, amount, prev_amount) > Hint — LAG(amount, 1, 0) OVER (ORDER BY month) returns the previous > row's amount, defaulting to 0 if none exists.
Write a SQL query that ranks every employee by salary within their department using dense ranking (no gaps when salaries are tied). Order the output by department, then rank, then name. Table: employees | id | name | salary | dept | |----|-------|--------|-------------| | 1 | Alice | 90000 | Engineering | | 2 | Bob | 90000 | Engineering | | 3 | Carol | 75000 | Engineering | | 4 | Dave | 80000 | Marketing | | 5 | Eve | 80000 | Marketing | | 6 | Frank | 60000 | Marketing | Alice and Bob are tied → both rank 1; Carol is rank 2 (dense, no gap). Expected output (columns: name, dept, salary, rank) > DENSE_RANK vs RANK — RANK skips numbers after a tie (1,1,3); > DENSE_RANK never skips (1,1,2).
Write a SQL query using a CTE (Common Table Expression) to find customers who placed at least one order in 2023 but no orders in 2024 — i.e. they churned. Return their names ordered alphabetically. Table: orders | id | customer | order_date | |----|----------|------------| | 1 | Alice | 2023-03-10 | | 2 | Bob | 2023-07-22 | | 3 | Alice | 2024-01-15 | | 4 | Carol | 2023-11-05 | | 5 | Dave | 2023-06-30 | | 6 | Dave | 2024-03-20 | Alice and Dave ordered in 2024 → retained. Bob and Carol did not → churned. Expected output (column: customer) > Hint — define two CTEs (active_2023, active_2024), then LEFT JOIN > and filter on IS NULL.