Quick Overview

This question evaluates SQL data-manipulation and analytic competencies including join semantics, set operations, window functions, view versus materialized view trade-offs, key and partition design, and query-tuning techniques in the Data Manipulation (SQL/Python) domain.

Compute join counts and window ranks

Company: Amazon

Role: Data Scientist

Category: Data Manipulation (SQL/Python)

Difficulty: medium

Interview Round: Onsite

Given the following small schema and data, answer all parts precisely and justify each count/output. Tables and rows: Customers(cust_id INT PRIMARY KEY, name TEXT) cust_id | name --------+------ 1 | Alice 2 | Bob 3 | Chen 4 | Dana Orders(order_id INT PRIMARY KEY, customer_id INT, amount INT) order_id | customer_id | amount ---------+-------------+------- 101 | 1 | 50 102 | 1 | 70 103 | 2 | 30 104 | 5 | 99 A(val INT) val ---- 1 2 2 3 B(val INT) val ---- 2 3 4 Scores(user_id INT, score INT, ts DATE) user_id | score | ts --------+-------+----------- 1 | 95 | 2025-08-30 1 | 88 | 2025-08-31 2 | 88 | 2025-08-31 3 | 75 | 2025-08-31 Tasks: 1) For SELECT * FROM Customers c JOIN Orders o ON c.cust_id = o.customer_id, provide the exact row counts for INNER JOIN, LEFT JOIN, RIGHT JOIN, FULL OUTER JOIN, and CROSS JOIN. Briefly explain each count (e.g., duplicate-preserving joins, unmatched rows, null-extended rows). 2) For SELECT val FROM A UNION SELECT val FROM B and SELECT val FROM A UNION ALL SELECT val FROM B: give (a) the row count and (b) the final sorted contents for each query. 3) On 2025-08-31 only, compute both RANK() and DENSE_RANK() over (ORDER BY score DESC) for Scores, and list the expected (user_id, score, rank, dense_rank) rows for that date. 4) Define a VIEW and contrast it with a MATERIALIZED VIEW. Give one pro and one con of each in analytic workloads. In this schema, propose a useful view and specify its definition. 5) Define PRIMARY KEY, FOREIGN KEY, and PARTITION KEY. If Orders grows to 1B rows with an added order_date DATE, choose appropriate keys and a partitioning strategy. Name at least three DB-agnostic query-tuning steps that would speed queries filtering on order_date and joining on customer_id (e.g., predicate pushdown, covering indexes, statistics, join reordering). Then rewrite this naive query for performance on large tables and explain why your rewrite should help: Naive: SELECT * FROM Orders o JOIN Customers c ON o.customer_id = c.cust_id WHERE o.amount > 40 ORDER BY c.name;

Overview: This question evaluates SQL data-manipulation and analytic competencies including join semantics, set operations, window functions, view versus materialized view trade-offs, key and partition design, and query-tuning techniques in the Data Manipulation (SQL/Python) domain.

Read the full Amazon Data Scientist interview experience this question came from

Join row counts across join types

Using the tables Customers and Orders below, compute the exact output row count for each of the following joins between Customers c and Orders o on c.cust_id = o.customer_id: - INNER JOIN - LEFT JOIN - RIGHT JOIN - FULL OUTER JOIN - CROSS JOIN Return one row per join type with columns (join_type, row_count).

Tables

Customers(cust_id INT, name VARCHAR(50))

Orders(order_id INT, customer_id INT, amount INT)

Hints

  1. Remember that joins preserve duplicates: one customer with two matching orders produces two rows.
  2. A LEFT JOIN adds one NULL-extended row per unmatched left-side row.

UNION vs UNION ALL row counts and contents

Using tables A and B below, compute the final sorted contents and row count for each query: 1) SELECT val FROM A UNION SELECT val FROM B 2) SELECT val FROM A UNION ALL SELECT val FROM B Return results in a single result set with columns (query_name, val, total_rows), where total_rows is the total number of rows for that query.

Tables

A(val INT)

B(val INT)

Hints

  1. UNION removes duplicates across the combined set.
  2. UNION ALL preserves all rows (including duplicates).

RANK vs DENSE_RANK on a single date

Using the Scores table below, consider only rows where ts = '2025-08-31'. Compute: - RANK() OVER (ORDER BY score DESC) - DENSE_RANK() OVER (ORDER BY score DESC) Return (user_id, score, rank, dense_rank) ordered by score DESC, then user_id ASC.

Tables

Scores(user_id INT, score INT, ts DATE)

Hints

  1. Filter to ts = '2025-08-31' before computing the window functions.
  2. RANK() skips ranks after ties; DENSE_RANK() does not.

Create a customer order summary view

Create a VIEW named v_customer_order_summary that returns one row per customer with: - cust_id - name - order_count (number of matching orders) - total_amount (sum of matching order amounts) Include customers with no orders, and show 0 for order_count and total_amount in that case. After creating the view, query it ordered by cust_id.

Tables

Customers(cust_id INT, name VARCHAR(50))

Orders(order_id INT, customer_id INT, amount INT)

Hints

  1. Use a LEFT JOIN so customers without orders still appear.
  2. Aggregate with COUNT(order_id) and SUM(amount), then COALESCE to 0.

Rewrite a join query to be more selective and avoid SELECT *

Given the Customers and Orders tables below, rewrite this naive query to return only needed columns and apply filtering as early as possible: Naive query: SELECT * FROM Orders o JOIN Customers c ON o.customer_id = c.cust_id WHERE o.amount > 40 ORDER BY c.name; Your rewritten query must return columns (order_id, order_date, amount, cust_id, name) and preserve the same results on the sample data. Note: Orders includes a row with customer_id that does not exist in Customers; the query uses an inner join so that row should not appear.

Tables

Customers(cust_id INT, name VARCHAR(50))

Orders(order_id INT, customer_id INT, amount INT, order_date DATE)

Hints

  1. Avoid SELECT *; project only the columns you need.
  2. Filter Orders before joining so fewer rows participate in the join.

Loading coding console...