Coding questions · Amazon

Diagnose MySQL joins and GROUP BY/HAVING errors

This question evaluates mastery of SQL join semantics (LEFT, RIGHT, FULL OUTER emulation, CROSS), NULL equality and duplicate handling, GROUP BY/HAVING behavior under ONLY_FULL_GROUP_BY, aggregation result shaping and type/format conversions in the Data Manipulation (SQL/Python) domain.

You are using MySQL 8.0 with ONLY_FULL_GROUP_BY enabled. Answer all parts precisely. Provide the exact SQL you would run and the final result shapes/values where requested.

Schema (use these small samples exactly as given):

Table A
+----+------+
| id | valA |
+----+------+
| 1 | 'x' |
| 2 | 'y' |
| 3 | 'z' |
| 4 | NULL|
+----+------+

Table B
+----+------+
| id | valB |
+----+------+
| 2 | 'p' |
| 3 | 'q' |
| 3 | 'r' |
| 5 | 's' |
|NULL| 't' |
+----+------+

Table t
+------+------+
| a | b |
+------+------+
| 1 | 'u' |
| 1 | 'v' |
| 1 | 'v' |
| 2 | 'w' |
| NULL | 'x' |
+------+------+

Table users
+---------+---------+
| user_id | name |
+---------+---------+
| 1 | 'Ann' |
| 2 | 'Bob' |
| 3 | 'Cara' |
+---------+---------+

Table orders
+----------+---------+---------------------+--------------+
| order_id | user_id | created_at | amount_cents |
+----------+---------+---------------------+--------------+
| 10 | 1 | '2025-08-30 10:00' | 1200 |
| 11 | 1 | '2025-08-31 09:30' | 300 |
| 12 | 2 | '2025-09-01 12:45' | 4500 |
+----------+---------+---------------------+--------------+

Table events
+-----------+------------------+
| event_id | duration_seconds |
+-----------+------------------+
| 100 | 59 |
| 101 | 61 |
| 102 | 90061 |
+-----------+------------------+

Table sessions
+------------+---------+---------------------+---------------------+
| session_id | user_id | login_at | logout_at |
+------------+---------+---------------------+---------------------+
| 1000 | 1 | '2025-08-31 23:50' | '2025-09-01 00:10:40'|
| 1001 | 2 | '2025-09-01 09:00' | '2025-09-01 09:00' |
| 1002 | 3 | '2025-09-01 10:15' | NULL |
+------------+---------+---------------------+---------------------+

A) Explain precisely what LEFT JOIN, RIGHT JOIN, FULL OUTER JOIN, and CROSS JOIN mean in terms of set semantics and duplicate handling. Then, using A and B:

Write queries for each join type (note: MySQL lacks FULL OUTER JOIN—emulate it with correct SQL).
For each join type, give the exact row count returned and list the rows for the first three results when ordered by COALESCE(A.id, B.id), B.valB, A.valA. Explain how NULLs affect equality joins here.

B) For the following MySQL queries over table t, indicate for each whether it executes or errors under ONLY_FULL_GROUP_BY. If it errors, state the exact reason (e.g., nonaggregated column not in GROUP BY, clause order, reserved word usage). Do not change the text. Q1: SELECT a, COUNT() FROM t GROUP BY a HAVING COUNT() > 2; Q2: SELECT a, b, COUNT() FROM t GROUP BY a HAVING COUNT() > 2; Q3: SELECT a, COUNT() AS cnt FROM t HAVING cnt > 2 GROUP BY a ORDER BY 1,2; Q4: SELECT a, COUNT() AS table FROM t GROUP BY a HAVING table > 2 ORDER BY 1,2; Q5: SELECT a, COUNT(*) AS table FROM t GROUP BY a HAVING table > 2 ORDER BY 1,2;

C) Write a single query that returns, for every user (including those with zero orders): user_id, name, orders_count, total_spend_dollars (amount_cents/100 with two decimals). Sort by total_spend_dollars DESC then user_id ASC. Ensure users with no orders appear with 0 and 0.00.

D) Convert duration_seconds in events to a human-readable string exactly in the form 'X days Y hours Z minutes W seconds' using integer arithmetic (no loops/UDFs). For example, 90061 should render '1 days 1 hours 1 minutes 1 seconds'. Provide the SELECT that produces both the original seconds and the string.

E) Using sessions, output session_id, user_id, hhmmss_duration (as TIME) and duration_seconds (as INT). Use TIMEDIFF and TIME_TO_SEC. Treat NULL logout_at as exclude-from-output (i.e., only return rows with non-NULL logout_at). Also explain how your query would change if you had to treat NULL logout_at as NOW().

Understanding Join Types and NULL Handling in MySQL

Join Types and NULL Handling

You are given two tables, A and B.

A(id, val_a) — id is the primary key (never NULL); val_a may be NULL.
B(id, val_b) — id may be NULL and is not unique (duplicate ids allowed); val_b may be NULL.

Conceptual part (explain in words): Describe what LEFT JOIN, RIGHT JOIN, FULL OUTER JOIN, and CROSS JOIN mean in terms of (a) set semantics — which input rows are guaranteed to appear — and (b) duplicate handling — how many output rows appear when a key matches multiple rows on the other side. Also note how the equality predicate A.id = B.id treats NULL (since NULL = NULL evaluates to UNKNOWN, NULL keys never match and only appear through outer-join padding).

Query part: Write one SQL query that produces the result of all four join types over A and B (joining on A.id = B.id for the three equality joins, and the unconditional Cartesian product for CROSS), stacked into a single result set. The output must have exactly these five columns:

column	meaning
`join_type`	one of `'CROSS'`, `'FULL'`, `'LEFT'`, `'RIGHT'` identifying which join produced the row
`a_id`	`A.id` for the row (NULL when the row came from B with no A match)
`a_val_a`	`A.val_a` for the row
`b_id`	`B.id` for the row (NULL when the row came from A with no B match)
`b_val_b`	`B.val_b` for the row

For FULL, emulate a full outer join by combining the LEFT JOIN result with the rows of the RIGHT JOIN result that have no A match (a_id IS NULL), de-duplicating exact duplicates (use UNION).

Each output row represents one row of the corresponding join. Order the combined output by join_type, then COALESCE(a_id, b_id), then b_val_b, then a_val_a (all ascending; PostgreSQL sorts NULLs last for ascending order).

Table `A`

column	type	constraints
id	INT	PRIMARY KEY, NOT NULL
val_a	VARCHAR(10)

Sample data:

id	val_a
1	x
2	y
3	z
4	NULL

Table `B`

column	type	constraints
id	INT
val_b	VARCHAR(10)

Sample data:

id	val_b
2	p
3	q
3	r
5	s
NULL	t

Expected output

37 rows: 20 CROSS (4x5 Cartesian product), 7 FULL (emulated), 5 LEFT, 5 RIGHT, with columns join_type, a_id, a_val_a, b_id, b_val_b, ordered by join_type, COALESCE(a_id,b_id), b_val_b, a_val_a.

a_id	a_val_a	b_id	b_val_b	join_type
1	x	2	p	CROSS
1	x	3	q	CROSS
1	x	3	r	CROSS
1	x	5	s	CROSS
1	x	NULL	t	CROSS
2	y	2	p	CROSS
2	y	3	q	CROSS
2	y	3	r	CROSS
2	y	5	s	CROSS
2	y	NULL	t	CROSS
3	z	2	p	CROSS
3	z	3	q	CROSS
3	z	3	r	CROSS
3	z	5	s	CROSS
3	z	NULL	t	CROSS
4	NULL	2	p	CROSS
4	NULL	3	q	CROSS
4	NULL	3	r	CROSS
4	NULL	5	s	CROSS
4	NULL	NULL	t	CROSS
1	x	NULL	NULL	FULL
2	y	2	p	FULL
3	z	3	q	FULL
3	z	3	r	FULL
4	NULL	NULL	NULL	FULL
NULL	NULL	5	s	FULL
NULL	NULL	NULL	t	FULL
1	x	NULL	NULL	LEFT
2	y	2	p	LEFT
3	z	3	q	LEFT
3	z	3	r	LEFT
4	NULL	NULL	NULL	LEFT
2	y	2	p	RIGHT
3	z	3	q	RIGHT
3	z	3	r	RIGHT
NULL	NULL	5	s	RIGHT
NULL	NULL	NULL	t	RIGHT

Hints

Build one CTE per join type, all projecting the same columns, then stack them with UNION ALL and tag each with a literal join_type string.
Emulate FULL OUTER JOIN as the LEFT JOIN result UNION the RIGHT JOIN rows that have no left match (a_id IS NULL).
PostgreSQL rejects an expression like COALESCE(...) in an ORDER BY placed directly on a UNION chain — wrap the union in a subquery/CTE and apply ORDER BY in the outer SELECT.

Diagnosing ONLY_FULL_GROUP_BY Errors for GROUP BY / HAVING

You are using MySQL 8.0 with ONLY_FULL_GROUP_BY enabled. For each of the following queries over table t, state whether it executes successfully or errors. If it errors, specify why (e.g., nonaggregated column not in GROUP BY, clause order, reserved word usage). Do not change the query text.

Q1: SELECT a, COUNT() FROM t GROUP BY a HAVING COUNT() > 2; Q2: SELECT a, b, COUNT() FROM t GROUP BY a HAVING COUNT() > 2; Q3: SELECT a, COUNT() AS cnt FROM t HAVING cnt > 2 GROUP BY a ORDER BY 1,2; Q4: SELECT a, COUNT() AS table FROM t GROUP BY a HAVING table > 2 ORDER BY 1,2; Q5: SELECT a, COUNT(*) AS table FROM t GROUP BY a HAVING table > 2 ORDER BY 1,2;

Table `t`

column	type	constraints
a	INT
b	VARCHAR(10)

Sample data:

a	b
1	u
1	v
1	v
2	w
NULL	x

Expected output

For each query Q1–Q5, we classify it as either 'executes' or 'errors' under ONLY_FULL_GROUP_BY and give the reason. Q1 is valid and returns a single row (a = 1, COUNT() = 3). Q2 errors because b is neither aggregated nor listed in GROUP BY. Q3 has a syntax error because HAVING appears before GROUP BY. Q4 is valid; the alias table in HAVING refers to COUNT(), and reserved words can be used as column aliases. Q5 is also valid; the backticked alias table behaves the same as in Q4.

details	query_name	status
Valid GROUP BY; returns 1 row where a = 1 and COUNT(*) = 3	Q1	executes
ONLY_FULL_GROUP_BY violation: nonaggregated column b is selected but not in GROUP BY	Q2	errors
Syntax error: HAVING clause appears before GROUP BY	Q3	errors
Valid; alias table in HAVING refers to COUNT(*); returns 1 row where a = 1 and table = 3	Q4	executes
Valid; backticked alias `table` in HAVING refers to COUNT(*); returns 1 row where a = 1 and `table` = 3	Q5	executes

Hints

ONLY_FULL_GROUP_BY requires every nonaggregated column in the SELECT list to be named in the GROUP BY.
Check the legal clause order in a SELECT statement: FROM, WHERE, GROUP BY, HAVING, ORDER BY, LIMIT.

Users with Order Counts and Total Spend

Write a single MySQL query that returns, for every user (including those with zero orders): user_id, name, orders_count, and total_spend_dollars. total_spend_dollars should be the sum of amount_cents divided by 100 with exactly two decimal places. Users with no orders must appear with orders_count = 0 and total_spend_dollars = 0.00. Sort the result by total_spend_dollars in descending order, then by user_id in ascending order.

Table `users`

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

Sample data:

name	user_id
Ann	1
Bob	2
Cara	3

Table `orders`

column	type	constraints
order_id	INT	PRIMARY KEY, NOT NULL
user_id	INT	NOT NULL
created_at	DATETIME	NOT NULL
amount_cents	INT	NOT NULL

Sample data:

amount_cents	created_at	order_id	user_id
1200	2025-08-30 10:00	10	1
300	2025-08-31 09:30	11	1
4500	2025-09-01 12:45	12	2

Expected output

All users are listed regardless of whether they have orders. For each user, orders_count is the number of orders and total_spend_dollars is the sum of amount_cents / 100.00 rounded to two decimals. With the sample data, Bob has one order for $45.00, Ann has two orders totaling $15.00, and Cara has none. Sorted by total_spend_dollars DESC then user_id ASC, the result is:

name	orders_count	total_spend_dollars	user_id
Bob	1	45	2
Ann	2	15	1
Cara	0	0	3

Hints

Use a LEFT JOIN from users to orders so that users with no orders are still returned.
Remember that SUM over no rows is NULL; use COALESCE to turn it into 0.00.

Formatting Durations as Days/Hours/Minutes/Seconds

Using the events table, write a PostgreSQL query that converts each event's duration_seconds into a human-readable string of the exact form 'X days Y hours Z minutes W seconds', using only integer arithmetic (no loops, no user-defined functions, no interval/date functions).

The decomposition uses fixed conversions: 86400 seconds per day, 3600 per hour, 60 per minute. For example, 90061 seconds renders as '1 days 1 hours 1 minutes 1 seconds', and 61 seconds renders as '0 days 0 hours 1 minutes 1 seconds'. The unit words are always plural (no special-casing for 0 or 1).

Return one row per event with these three columns:

event_id — the event's id
duration_seconds — the original (unchanged) duration in seconds
human_readable — the formatted string described above

Sort the result by event_id ascending.

Table `events`

column	type	constraints
event_id	INT	PRIMARY KEY, NOT NULL
duration_seconds	INT	NOT NULL

Sample data:

duration_seconds	event_id
59	100
61	101
90061	102
0	103
86400	104

Expected output

One row per event with the original duration and its 'X days Y hours Z minutes W seconds' decomposition, ordered by event_id.

duration_seconds	event_id	human_readable
59	100	0 days 0 hours 0 minutes 59 seconds
61	101	0 days 0 hours 1 minutes 1 seconds
90061	102	1 days 1 hours 1 minutes 1 seconds
0	103	0 days 0 hours 0 minutes 0 seconds
86400	104	1 days 0 hours 0 minutes 0 seconds

Hints

There are 86400 seconds in a day, 3600 in an hour, and 60 in a minute.
In PostgreSQL, integer division is / (on integer operands) and modulo is % — there is no DIV operator and MOD is a function, not an infix operator.
Subtract each larger unit before computing the next: take % 86400 to get the remainder after full days, then divide by 3600 for hours, and so on.

Computing Session Durations with TIMEDIFF and TIME_TO_SEC

Compute closed-session durations

You are given a sessions table that records when each user logged in and out.

column	type	notes
`session_id`	INT	primary key
`user_id`	INT	who the session belongs to
`login_at`	TIMESTAMP	when the session started (never NULL)
`logout_at`	TIMESTAMP	when the session ended; NULL means the session is still open

Write a PostgreSQL query that, for every closed session (a session where logout_at IS NOT NULL), returns its duration in two forms:

session_id
user_id
hhmmss_duration — the elapsed time formatted as a HH:MI:SS text string (e.g. 00:20:40)
duration_seconds — the same elapsed time as a whole number of seconds (INT)

Exclude sessions that are still open (logout_at IS NULL). Sort the result by session_id ascending.

Follow-up (describe, don't code): how would the query change if open sessions should instead be treated as ending now rather than excluded?

Table `sessions`

column	type	constraints
session_id	INT	PRIMARY KEY, NOT NULL
user_id	INT	NOT NULL
login_at	TIMESTAMP	NOT NULL
logout_at	TIMESTAMP

Sample data:

login_at	logout_at	session_id	user_id
2025-08-31 23:50:00	2025-09-01 00:10:40	1000	1
2025-09-01 09:00:00	2025-09-01 09:00:00	1001	2
2025-09-01 10:15:00	NULL	1002	3
2025-09-01 14:00:00	2025-09-01 15:30:05	1003	4

Expected output

One row per closed session (logout_at not NULL), with duration as HH:MI:SS text and as integer seconds, ordered by session_id. Session 1000 = 20m40s (1240s), 1001 = 0s, 1003 = 1h30m05s (5405s); session 1002 is excluded (open).

duration_seconds	hhmmss_duration	session_id	user_id
1240	00:20:40	1000	1
0	00:00:00	1001	2
5405	01:30:05	1003	4

Hints

Subtracting two TIMESTAMP columns (logout_at - login_at) gives an interval — Postgres's answer to MySQL's TIMEDIFF.
TO_CHAR(interval, 'HH24:MI:SS') formats the interval; EXTRACT(EPOCH FROM interval)::int gives total seconds (replacing TIME_TO_SEC).
Filter with WHERE logout_at IS NOT NULL and sort by session_id for a deterministic result.