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