This tripped me up more than it should have.
First, clarify the table structures and row counts, noting the orphaned order. Then, systematically explain how each join type handles the unmatched rows, and compute the resulting row counts based on the join semantics.
Pro tip: Emphasize that the orphaned order is the key differentiator: it appears in RIGHT and FULL OUTER joins but not INNER or LEFT. Also, mention that CROSS JOIN produces the Cartesian product regardless of matching.
State the assumed row counts: Customers has N rows, Orders has M rows, with one order having a customer_id not in Customers. Confirm that all other orders have valid customer references.
INNER JOIN returns only matching rows. Since one order is orphaned, it is excluded, so the count is M - 1 (assuming each order matches exactly one customer).
LEFT JOIN returns all customers and matching orders. Customers without orders get NULLs, but the orphaned order is not included because it has no matching customer. Count is N + (M - 1) if every customer has at least one order? Actually, need to consider customers with no orders: each such customer adds 1 row. So count = (number of customers with at least one order) + (M - 1) + (number of customers with no orders) = N + (M - 1). Wait, careful: LEFT JOIN returns all rows from left table (Customers) plus matching rows from right. For each customer, if they have k orders, they appear k times; if 0 orders, they appear once with NULLs. So total rows = sum over customers of max(1, number of orders) = (number of customers with orders) + (M - 1) + (number of customers without orders) = N + (M - 1). Because each customer with orders contributes their order count, and those without contribute 1. Since total orders M-1 are matched, and there are N customers, the sum of order counts for customers with orders is M-1. So total = (M-1) + (N - number of customers with orders) + (number of customers with orders) = N + M - 1. Yes.
RIGHT JOIN returns all orders and matching customers. The orphaned order appears with NULL customer. So count = M (all orders) plus any customers without orders? No, RIGHT JOIN returns all rows from right table (Orders) and matching from left. So each order appears once, with customer info if match, else NULL. So count = M. (Customers without orders are not included.)
FULL OUTER JOIN combines LEFT and RIGHT: all customers and all orders, with NULLs where no match. Count = (N + M - 1) + 1? Actually, FULL OUTER JOIN returns all rows from both tables, with matching rows combined. The orphaned order appears once with NULL customer. Customers without orders appear once with NULL order. So total = (M - 1) matched rows + 1 orphaned order + (N - number of customers with orders) customers without orders. But number of customers with orders = number of distinct customers in Orders (excluding orphan) = let's call it C. Then total = (M - 1) + 1 + (N - C) = M + N - C. But C is not necessarily N. However, if we assume all customers except possibly some have orders, then C ≤ N. But the question likely expects a formula in terms of N and M. Alternatively, if we assume every customer has at least one order except possibly some, then C = N - (customers without orders). So total = M + N - C = M + (customers without orders). But without knowing C, we can't give a numeric answer. However, the question asks for exact row counts given the scenario. Perhaps they expect: INNER = M-1, LEFT = N + M - 1, RIGHT = M, FULL = N + M, CROSS = N * M. But is FULL = N + M? Let's check: If we have N customers and M orders, with one orphan, then FULL OUTER JOIN will have: for each matching pair (M-1 rows), plus one row for the orphan order, plus one row for each customer without orders. So total = (M-1) + 1 + (N - C) = M + N - C. If we assume that every customer has at least one order, then C = N, so total = M. But that contradicts. Actually, if every customer has at least one order, then there are no customers without orders, so FULL = (M-1) + 1 = M. But that would mean FULL equals RIGHT, which is not generally true. So the assumption matters. The question likely expects the candidate to recognize that the exact counts depend on the number of customers and orders, and the number of customers without orders. But the question says 'Given a Customers table and an Orders table (with one order referencing a non-existent customer)', it doesn't specify if all customers have orders. So the candidate should state the formulas in terms of N, M, and possibly the number of customers without orders. However, many interviewers expect the candidate to assume that every customer has at least one order (so no customers without orders) to simplify. But then LEFT JOIN would be N + M - 1? If every customer has at least one order, then number of customers with orders = N, so LEFT = (M-1) + (N - N) = M-1? That's wrong. Let's re-evaluate: If every customer has at least one order, then there are no customers without orders. Then LEFT JOIN: all customers appear, each with their orders. Total rows = sum of order counts per customer = M-1 (since orphan not included). But that would be M-1, which is less than N if N > M-1? That can't be because LEFT JOIN must include all customers. Actually, if every customer has at least one order, then the number of orders M-1 must be at least N. So M-1 ≥ N. Then LEFT JOIN returns exactly M-1 rows? No, because each customer appears as many times as they have orders, so total rows = total number of orders from valid customers = M-1. But that means customers with multiple orders appear multiple times, and customers with one order appear once. So total rows = M-1. But then the number of rows is less than N? That's impossible because each customer must appear at least once. So if every customer has at least one order, then M-1 ≥ N, and total rows = M-1, which is ≥ N. So it's possible. But then LEFT JOIN count = M-1. However, that would mean LEFT JOIN and INNER JOIN have the same count if every customer has at least one order? No, INNER JOIN also returns M-1 rows. So they are the same. That is a special case. But the question likely expects a more general answer. To avoid confusion, the candidate should state the assumptions clearly. A common assumption is that there are N customers and M orders, with one orphan, and that some customers may have no orders. Then the counts are: INNER = M-1, LEFT = N + M - 1 - (number of customers with no orders)? Actually, LEFT = (M-1) + (number of customers with no orders). Because each customer with orders contributes their order count, and those without contribute 1. So total = (M-1) + (N - C), where C is number of customers with at least one order. But C is not given. So the exact count cannot be determined without knowing C. However, the question asks for 'exact row counts', implying that the answer should be in terms of N and M, perhaps assuming that every customer has at least one order? But then LEFT = M-1, which is not N+M-1. So there is ambiguity. The best approach is to state the formulas in terms of N, M, and the number of customers without orders (call it K). Then: INNER = M-1, LEFT = M-1 + K, RIGHT = M, FULL = M + K, CROSS = N*M. Because FULL = LEFT + RIGHT - INNER = (M-1+K) + M - (M-1) = M + K. That makes sense. So the candidate should define K as the number of customers with no orders. Then the counts are as above. If the interviewer expects a numeric answer, they might assume K=0 (every customer has at least one order), then INNER = M-1, LEFT = M-1, RIGHT = M, FULL = M, CROSS = N*M. But that seems odd because LEFT and INNER would be the same. Alternatively, they might assume that there are no customers without orders, but then LEFT JOIN would still include all customers, so if every customer has at least one order, then the number of orders M-1 must be at least N,
AI-generated suggestions, not part of the candidate's original notes. May be inaccurate — verify before relying on them.
Straightforward once you remember UNION deduplicates.
First, clarify that UNION removes duplicate rows across the combined result, while UNION ALL retains all rows including duplicates. Then, manually compute the row counts and list the contents for each operation, explaining the deduplication logic step by step.
Pro tip: Mention that UNION typically requires a sort or hash operation to remove duplicates, which can be expensive on large datasets, so UNION ALL is preferred when duplicates are acceptable or known to be absent.
State that UNION combines rows from both tables and removes duplicates, while UNION ALL combines all rows without removing duplicates.
Write out the rows from table A (1, 2, 2, 3) and table B (2, 3, 4) to visualize the combined dataset.
Concatenate all rows: 1, 2, 2, 3, 2, 3, 4. Count the rows: 7. Note that duplicates are preserved.
Remove duplicate values from the combined list. The distinct values are 1, 2, 3, 4. Count the rows: 4.
Present the final counts and contents: UNION returns 4 rows (1,2,3,4); UNION ALL returns 7 rows (1,2,2,3,2,3,4). Highlight the difference in duplicate handling.
AI-generated suggestions, not part of the candidate's original notes. May be inaccurate — verify before relying on them.
First, clarify the table schema and the filter condition (date = '2025-08-31'). Then, explain how RANK() and DENSE_RANK() work over ORDER BY score DESC, and finally compute the expected output rows by hand, showing ties and gaps.
Pro tip: Mention that RANK() skips numbers after ties (e.g., 1,1,3) while DENSE_RANK() does not (e.g., 1,1,2). Also, note that if the table has duplicate scores, the output will reflect that, and if there are multiple users with the same score, their order among themselves is nondeterministic unless a tiebreaker is added.
Assume the Scores table has columns user_id, score, and date. Filter rows where date = '2025-08-31'.
RANK() assigns the same rank to ties but leaves gaps, while DENSE_RANK() assigns the same rank to ties without gaps. Both are computed over ORDER BY score DESC.
Sort the filtered rows by score descending. Assign ranks and dense ranks accordingly, handling ties appropriately.
List each row with user_id, score, rank, and dense_rank. If multiple users share a score, show that they get the same rank and dense_rank.
AI-generated suggestions, not part of the candidate's original notes. May be inaccurate — verify before relying on them.
A view is just a saved query, no data stored, always fresh but re-executes on every call.
Start by clearly defining both VIEW and MATERIALIZED VIEW, emphasizing that a VIEW is a virtual table (query stored, not data) while a MATERIALIZED VIEW physically stores the query result. Then discuss trade-offs for analytic workloads: VIEW offers real-time data but may be slow for complex queries; MATERIALIZED VIEW offers fast query performance but requires refresh and may be stale. Finally, propose a view definition that addresses a common analytical need, such as aggregating sales data by day, and explain why it's useful.
Pro tip: Mention that in Amazon's context, services like Redshift support materialized views with automatic refresh, and consider discussing how you'd choose between them based on data freshness requirements and query patterns. Also, highlight that materialized views can be used for pre-aggregation to reduce cost and improve performance in a data warehouse.
Explain that a VIEW is a saved query that acts as a virtual table, while a MATERIALIZED VIEW stores the query result physically on disk.
For VIEW: pro - always up-to-date, no storage cost; con - can be slow for complex queries. For MATERIALIZED VIEW: pro - fast query performance, can pre-aggregate; con - requires refresh, may be stale, uses storage.
Discuss scenarios: use VIEW for ad-hoc, real-time analysis on small data; use MATERIALIZED VIEW for repetitive, complex aggregations on large data where slight staleness is acceptable.
Given the schema (assume tables like sales, customers, products), propose a view that aggregates sales by day and product category, for example, to support trend analysis.
Justify that this view simplifies complex queries, improves performance for dashboards, and can be materialized if needed for further speedup.
AI-generated suggestions, not part of the candidate's original notes. May be inaccurate — verify before relying on them.
The rewrite part is where I spent most of my time.
Start by clearly defining the three key types (PRIMARY, FOREIGN, PARTITION) with a focus on their roles in relational databases and data warehousing. Then, for the 1 billion row scenario, propose a partitioning strategy (e.g., range partitioning on order_date) and discuss query tuning techniques like indexing, partitioning pruning, and query rewriting. Finally, rewrite the naive query to be more efficient by selecting only necessary columns, ensuring proper indexing, and considering join order and filtering.
Pro tip: Emphasize that partitioning is not just about storage but also about query performance; always align partitioning with common query predicates. Also, mention that at Amazon's scale, you'd consider columnar storage and distributed query engines like Redshift or Athena.
Clearly define PRIMARY KEY (unique identifier for a row), FOREIGN KEY (referential constraint linking to another table's primary key), and PARTITION KEY (column used to horizontally partition data for manageability and performance).
For 1 billion rows with order_date, recommend range partitioning on order_date (e.g., monthly or yearly) to enable partition pruning and easier data management. Consider sub-partitioning by customer_id if needed for even distribution.
Discuss creating indexes on join and filter columns (e.g., customer_id, amount, order_date), using partition pruning, avoiding SELECT *, and analyzing query plans. Consider materialized views or summary tables for frequent queries.
Rewrite the query to select only needed columns, ensure indexes on join and filter columns, and possibly filter before join. Example: SELECT o.order_id, o.amount, c.name FROM Orders o JOIN Customers c ON o.customer_id = c.cust_id WHERE o.amount > 40 AND o.order_date >= '...' ORDER BY c.name;
Mention trade-offs: partitioning adds complexity but improves performance; indexing speeds reads but slows writes. For Amazon-scale, consider columnar storage (Redshift) or NoSQL if schema flexible.
AI-generated suggestions, not part of the candidate's original notes. May be inaccurate — verify before relying on them.