Final assessment

Question 1 of 12

• Every agent is also someone’s manager.
• The CEO’s manager_id is NULL, and NOT IN against a list containing NULL is never TRUE.
• agent_id is NULL for every ticket.
• NOT IN can’t be used with a subquery.

Question 2 of 12

• The label has no year, so January 2024 and January 2025 were merged into one group.
• strftime counted each order twice.
• January has more days than other months.
• The query needed ORDER BY to group correctly.

Question 3 of 12

• It should use a LEFT JOIN.
• GROUP BY must list customer_id.
• Nothing; each row is a customer.
• It counts orders; use COUNT(DISTINCT c.customer_id), or query customers alone if every customer should count.

Question 4 of 12

• SUM(o.shipping_fee)
• COUNT(*) as the number of orders
• SUM(r.refund_amount)
• SUM(DISTINCT o.shipping_fee)

Question 5 of 12

• It works; LEFT JOIN keeps every product regardless of WHERE.
• Products without 2025 returns disappear; the date test must move into the ON clause of the returns join.
• The query fails because LEFT JOIN can’t be chained.
• Refund totals double because of the date filter.

Question 6 of 12

• Define it once as a CTE and reference it in both steps.
• Copy the same subquery into both places.
• Use a correlated subquery for each row instead.
• Create a permanent table with the results.

Question 7 of 12

• ROW_NUMBER() OVER (PARTITION BY category ORDER BY units DESC)
• NTILE(3) OVER (PARTITION BY category ORDER BY units DESC)
• RANK() OVER (PARTITION BY category ORDER BY units DESC), keeping rank 3 or better
• DENSE_RANK() OVER (ORDER BY units DESC)

Question 8 of 12

• No risk; LAG looks up the month 12 months earlier by date.
• LAG returns 0 for the missing month, which is correct.
• Use LEAD instead of LAG.
• Every comparison after the gap shifts by a month; generate a calendar of months and LEFT JOIN revenue to it first.

Question 9 of 12

• AVG(revenue) OVER (ORDER BY month ROWS BETWEEN 2 PRECEDING AND CURRENT ROW)
• AVG(revenue) OVER (ORDER BY month ROWS BETWEEN 3 PRECEDING AND CURRENT ROW)
• AVG(revenue) OVER (PARTITION BY month)
• AVG(revenue) OVER (ORDER BY month)

Question 10 of 12

• WHERE order_date LIKE ‘2025%’
• WHERE order_date >= ‘2025-01-01’ AND order_date < ‘2026-01-01’
• WHERE date(order_date) BETWEEN ‘2025-01-01’ AND ‘2025-12-31’
• WHERE CAST(strftime(‘%Y’, order_date) AS INTEGER) = 2025

Question 11 of 12

• PostgreSQL doesn’t support LIKE.
• The city column was converted to a number.
• PostgreSQL needs double quotes around text values.
• PostgreSQL’s LIKE is case-sensitive, so ‘dubai’ doesn’t match ‘Dubai’; use ILIKE or the exact value.

Question 12 of 12

• Report both as they are; the numbers came from the database.
• Drop the December cohort and Jordan from the slides.
• Show December’s month 1 as not yet observable, and show Jordan’s growth next to its small absolute values.
• Average both with the other cohorts and countries to smooth them out.