Example queries for SQL Challenge
How many total orders by count did the Midwest Region have in 2016?
SELECT r.name as region, SUM(o.total) as total_orders FROM sqlchallenge1.region r JOIN sqlchallenge1.sales_reps sr ON sr.region_id = r.id JOIN sqlchallenge1.accounts a ON a.sales_rep_id = sr.id JOIN sqlchallenge1.orders o ON o.account_id = a.id AND DATE_PART('year',occurred_at) = 2016 GROUP BY 1
How many customers have a primary POC whose name is longer than the name of that customer's sales rep?
select count(*) from sqlchallenge1.accounts a join sqlchallenge1.sales_reps sr on a.sales_rep_id = sr.id where length(a.primary_poc) > length(sr.name)
How many accounts have a name ending in the letter 'y'?
SELECT a.name FROM sqlchallenge1.accounts a WHERE RIGHT(a.name,1) ILIKE 'y'
For Sales Reps with at least 2 orders, what is the name of the Sales Rep who went the longest time between their first and second order?
rep_orders as ( SELECT sr.name as sales_rep_name, a.sales_rep_id, o.occurred_at, ROW_NUMBER () OVER (PARTITION BY a.sales_rep_id ORDER BY o.occurred_at) as order_num FROM sqlchallenge1.accounts a JOIN sqlchallenge1.orders o ON o.account_id = a.id JOIN sqlchallenge1.sales_reps sr ON sr.id = a.sales_rep_id ) SELECT o1.sales_rep_name, o1.occurred_at as first_order_at, o2.occurred_at as second_order_at, EXTRACT(EPOCH FROM o2.occurred_at) - EXTRACT(EPOCH FROM o1.occurred_at) as seconds_to_second_order FROM rep_orders o1 JOIN rep_orders o2 ON o2.sales_rep_id = o1.sales_rep_id AND o2.order_num = 2 WHERE o1.order_num = 1 ORDER BY 4 DESC
Which sales rep is assigned to the most accounts? (Enter 'name'.)
select rep.id, rep.name, count(distinct accounts.id) from sqlchallenge1.accounts accounts join sqlchallenge1.sales_reps rep on accounts.sales_rep_id = rep.id group by 1,2 order by 3 desc