Checkpoint: Analytical SQL

Question 1 of 5

• SELECT customer_id, MAX(order_date), order_id FROM orders GROUP BY customer_id
• Filter WHERE ROW_NUMBER() OVER (PARTITION BY customer_id ORDER BY order_date DESC) = 1
• Use RANK() OVER (PARTITION BY customer_id ORDER BY order_date DESC) and keep rank 1
• Compute ROW_NUMBER() OVER (PARTITION BY customer_id ORDER BY order_date DESC, order_id DESC) in a CTE, then keep rn = 1

Question 2 of 5

• SUM(revenue) OVER (PARTITION BY day ORDER BY day)
• SUM(revenue) OVER (PARTITION BY strftime(‘%Y-%m’, day) ORDER BY day)
• SUM(revenue) OVER (ORDER BY day)
• SUM(revenue) OVER (PARTITION BY strftime(‘%Y-%m’, day))

Question 3 of 5

• c.category_id = tree.parent_id
• c.parent_id IS NULL
• c.parent_id = tree.category_id
• c.category_id = tree.category_id

Question 4 of 5

• Mobile shoppers who reach the cart abandon far more often, so the mobile checkout is worth investigating first.
• Mobile traffic is lower quality and should be cut from the ad budget.
• Desktop users spend more per order.
• The funnel data must be wrong because the rates differ.

Question 5 of 5

• The cohort was too small for percentages.
• Month numbers were computed with julianday instead of a month index.
• The numerator counts orders instead of distinct customers.
• LEFT JOIN was used instead of INNER JOIN.