Example queries for SQL Challenge
CHALLENGE 1
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
CHALLENGE 2
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)
CHALLENGE 3
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'
CHALLENGE 4
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
CHALLENGE 5
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