Kata SQL

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”.

NOTE: Your solution should use pure SQL. Ruby is used within the test cases to do the actual testing.

Solution:

select p.id, p.name, count(s.sale) as sale_count,
rank() over (partition by p.id order by p.id desc) as sale_rank
from people p inner join
sales s
on p.id = s.people_id
group by p.id;

Link

Reference

--

--

Get the Medium app

A button that says 'Download on the App Store', and if clicked it will lead you to the iOS App store
A button that says 'Get it on, Google Play', and if clicked it will lead you to the Google Play store
Isabelle

Isabelle

In love with telling stories with data