SQL TUTORIALS

Answer Key

1. Query used:
   WITH total_prices AS (
   SELECT order_id, SUM(item_price) AS total_price
   FROM orders.order_items
   GROUP BY order_id),

avg_price AS (
   SELECT AVG(total_price) AS avg_price
   FROM total_prices)

SELECT o.*
FROM orders.order AS o
JOIN total_prices AS tp ON o.order_id = tp.order_id
CROSS JOIN avg_price
WHERE total_price > avg_price.avg_price;
2. Query used:
   WITH total_prices AS (
   SELECT order_id, SUM(item_price) AS total_price
   FROM orders.order_items
   GROUP BY order_id),

max_price AS (
   SELECT AVG(total_price) AS highest_price
   FROM total_prices)

SELECT o.*
FROM orders.order AS o
JOIN total_prices AS tp ON o.order_id = tp.order_id
CROSS JOIN max_price
WHERE total_price = highest_price;
3. Query used:
   WITH filtered_orders AS (
   SELECT order_id, order_date
   FROM orders.orders AS o
   JOIN orders.customers AS c ON o.customer_id = c.customer_id
   WHERE state IN('CA', 'CO', 'CT'))

SELECT order_date, SUM(item_price) AS total_revenue
FROM filtered_orders AS fo
JOIN orders.order_items AS oi ON fo.order_id = oi.order_id
GROUP BY order_date;