SQL Basics: Simple JOIN and RANK

For this challenge you need to create a simple SELECT statement that will return all columns from the people table, and join to the sales table so that you can return the COUNT of all sales and RANK each person by their sale_count.

people table schema

• id
• name

sales table schema

• id
• people_id
• sale
• price

You should return all people fields as well as the sale count as "sale_count" and the rank as "sale_rank".

 

 

Solution:

SELECT 
  p.id, 
  p.name, 
  COUNT(s.sale) AS sale_count,
  RANK () OVER (ORDER BY COUNT(s.sale)) AS sale_rank 
FROM 
  people p
  JOIN sales s ON s.people_id = p.id 
GROUP BY p.id

 

Result:

id	name	sale_count	sale_rank
10	Roma Walker III	5	1
7	Miss Aubrey Bode	6	2
9	Forest Romaguera	9	3
5	Lia Towne MD	9	3
8	Muriel Ward	10	5
3	Mariam Maggio III	11	6
2	Hiram Shields	12	7
1	Anastacio Macejkovic	12	7
4	Trycia Kulas	13	9
6	Ms. Vincenza Ratke	13	9