Solve complete analysis problems

WITH orders(id, category, region, amount) AS (
  VALUES (1, 'Books', 'North', 20), (2, 'Games', 'South', 30),
         (3, 'Games', 'North', 15), (4, 'Books', 'North', 40),
         (5, 'Garden', 'South', 25), (6, 'Garden', 'North', 10)
)
SELECT region, COUNT(*) AS orders, SUM(amount) AS sales FROM orders GROUP BY region ORDER BY sales DESC, region;

WITH customers(id, name) AS (VALUES (1, 'Ada'), (2, 'Ben'), (3, 'Cy')),
sales(order_id, customer_id, amount) AS (VALUES (10, 1, 20), (11, 1, 15), (12, 2, 30), (13, 9, 40))
SELECT c.name, COUNT(*) AS orders FROM customers c JOIN sales s ON c.id = s.customer_id GROUP BY c.name HAVING COUNT(*) > 1 ORDER BY c.name;

WITH customers(id, name) AS (VALUES (1, 'Ada'), (2, 'Ben'), (3, 'Cy')),
sales(order_id, customer_id, amount) AS (VALUES (10, 1, 20), (11, 1, 15), (12, 2, 30), (13, 9, 40))
SELECT c.name FROM customers c WHERE NOT EXISTS (SELECT 1 FROM sales s WHERE s.customer_id = c.id) ORDER BY c.name;

WITH events(order_id, sequence, status) AS (VALUES (1, 1, 'New'), (1, 2, 'Paid'), (2, 1, 'New'), (2, 2, 'Cancelled'), (3, 1, 'New'), (3, 2, 'Shipped')),
ranked AS (SELECT *, ROW_NUMBER() OVER (PARTITION BY order_id ORDER BY sequence DESC) AS position FROM events)
SELECT order_id, status FROM ranked WHERE position = 1 ORDER BY order_id;

WITH signups(id, email) AS (VALUES (1, '[email protected]'), (2, '[email protected]'), (3, '[email protected]'))
SELECT email, COUNT(*) AS copies FROM signups GROUP BY email HAVING COUNT(*) > 1 ORDER BY email;

WITH orders(id, category, region, amount) AS (
  VALUES (1, 'Books', 'North', 20), (2, 'Games', 'South', 30),
         (3, 'Games', 'North', 15), (4, 'Books', 'North', 40),
         (5, 'Garden', 'South', 25), (6, 'Garden', 'North', 10)
),
targets(region, target) AS (VALUES ('North', 80), ('South', 60)),
actuals AS (SELECT region, SUM(amount) AS sales FROM orders GROUP BY region)
SELECT a.region, a.sales, t.target, CASE WHEN a.sales >= t.target THEN 'Met' ELSE 'Below' END AS result FROM actuals a JOIN targets t ON a.region = t.region ORDER BY a.region;