Compute join counts and window ranks

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;

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).

Table Customers

column	type	constraints
cust_id	INT	PRIMARY KEY, NOT NULL
name	VARCHAR(50)	NOT NULL

Sample data:

cust_id	name
1	Alice
2	Bob
3	Chen
4	Dana

Table Orders

column	type	constraints
order_id	INT	PRIMARY KEY, NOT NULL
customer_id	INT	NOT NULL
amount	INT	NOT NULL

Sample data:

amount	customer_id	order_id
50	1	101
70	1	102
30	2	103
99	5	104

Expected output

One row per join type showing the number of rows produced by that join on the given data.

join_type	row_count
INNER	3
LEFT	5
RIGHT	4
FULL_OUTER	6
CROSS	16

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.
3. A CROSS JOIN returns |Customers| × |Orders| rows.

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.

Table A

column	type	constraints
val	INT	NOT NULL

Sample data:

val
1
2
2
3

Table B

column	type	constraints
val	INT	NOT NULL

Sample data:

val
2
3
4

Expected output

Rows for both queries, each sorted by val, and annotated with the total number of rows for that query (UNION removes duplicates; UNION ALL preserves them).

query_name	total_rows	val
UNION	4	1
UNION	4	2
UNION	4	3
UNION	4	4
UNION_ALL	7	1
UNION_ALL	7	2
UNION_ALL	7	2
UNION_ALL	7	2
UNION_ALL	7	3
UNION_ALL	7	3
UNION_ALL	7	4

Hints

1. UNION removes duplicates across the combined set.
2. UNION ALL preserves all rows (including duplicates).
3. Use COUNT(*) OVER (PARTITION BY ...) to attach the total row count to each row.

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.

Table Scores

column	type	constraints
user_id	INT	NOT NULL
score	INT	NOT NULL
ts	DATE	NOT NULL

Sample data:

score	ts	user_id
95	2025-08-30	1
88	2025-08-31	1
88	2025-08-31	2
75	2025-08-31	3

Expected output

Ranks computed only within the date 2025-08-31. Tied scores share the same rank; RANK() skips numbers after ties while DENSE_RANK() does not.

dense_rank	rank	score	user_id
1	1	88	1
1	1	88	2
2	3	75	3

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.

Table Customers

column	type	constraints
cust_id	INT	PRIMARY KEY, NOT NULL
name	VARCHAR(50)	NOT NULL

Sample data:

cust_id	name
1	Alice
2	Bob
3	Chen
4	Dana

Table Orders

column	type	constraints
order_id	INT	PRIMARY KEY, NOT NULL
customer_id	INT	NOT NULL
amount	INT	NOT NULL

Sample data:

amount	customer_id	order_id
50	1	101
70	1	102
30	2	103
99	5	104

Expected output

Selecting from the view yields one row per customer (including those without orders) with counts and sums defaulted to 0.

cust_id	name	order_count	total_amount
1	Alice	2	120
2	Bob	1	30
3	Chen	0	0
4	Dana	0	0

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.

Table Customers

column	type	constraints
cust_id	INT	PRIMARY KEY, NOT NULL
name	VARCHAR(50)	NOT NULL

Sample data:

cust_id	name
1	Alice
2	Bob
3	Chen
4	Dana

Table Orders

column	type	constraints
order_id	INT	PRIMARY KEY, NOT NULL
customer_id	INT	NOT NULL
amount	INT	NOT NULL
order_date	DATE	NOT NULL

Sample data:

amount	customer_id	order_date	order_id
50	1	2025-08-30	101
70	1	2025-08-31	102
30	2	2025-08-31	103
99	5	2025-08-29	104

Expected output

Only orders with amount > 40 that successfully join to Customers, ordered by customer name (and then order_id for deterministic output).

amount	cust_id	name	order_date	order_id
50	1	Alice	2025-08-30	101
70	1	Alice	2025-08-31	102

Hints

1. Avoid SELECT *; project only the columns you need.
2. Filter Orders before joining so fewer rows participate in the join.
3. Keep the same inner join semantics so unmatched Orders rows are excluded.