CODEWITHSUNDEEP
sqldatabasesql-server-2008join
Tru
Tru

Reputation: 1467

How to get a MAX and a COUNT from a three table join?

I got an interview question where there's a Car sale modeled in a DB. Each Car represents a physical car in a Car sale which refers to a Make and a Model table. A Sale table keeps track of each Car that is sold. A Sale only consists of one Car, so there's a record in Sale per every unique Car that had been sold.

The question was to find-out the name of the most sold Model in the car sale. I answered with a 3-level nested query. The interviewer specifically asked for a solution using joins where I only succeeded in just joining the tables without the aggregates.

How would you join 3 tables as below (Car, Make, Sale) while using two other aggregates?

Here's a rough sketch of the schema. The most sold Model here should return 'Corolla'

Car

| carid|  modid | etc...
_________________
|  1   |    1   |
|  2   |    1   |
|  3   |    1   |
|  4   |    2   |
|  5   |    2   |

Make

| mkid |  name  |
_________________
|  1   | Toyota |
|  2   | Nissan |
|  3   |  Chevy |
|  4   |  Merc  |
|  5   |  Ford  |

Model

| modid|  name   | mkid |
________________________
|  1   |  Corolla|  1
|  2   |  Sunny  |  2
|  3   |  Carina |  1
|  4   |  Skyline|  2
|  5   |  Focus  |  5

Sale

|  sid |  carid | etc...
_________________
|  1   |    1   |
|  2   |    2   |
|  3   |    3   |
|  4   |    4   |
|  5   |    5   |

Edit:

Using MS SQL Server 2008

Output needed:

Model Name |  Count
_____________________
Corolla    |    3

i.e. The model of the Car that has been sold the most. Notice only 3 Corollas and 2 Sunnys are in the Car table while Sale table corresponds to each of those with other sales detail. The 5 Sale records are actually Corolla, Corolla, Corolla, Sunnnu and Sunny.

Upvotes: 0

Views: 620

Answers (4)

pyrospade
pyrospade

Reputation: 8076

Based on your newly added SQL Server 2008 tag. If you are using a different RDBMS you'll probably need to use limit instead of top and place it at the end of the top_sold_car subquery.

select Make.name as Make, Model.name as Model
from (
    select top 1 count(*) as num_sold
    from Car
    group by modid
    order by num_sold desc) as top_sold_car
join Model
    on (top_sold_car.modid = Model.modid)
join Make
    on (Model.mkid = Make.mkid)

Upvotes: 2

Priyank Doshi
Priyank Doshi

Reputation: 13161

Following query works on oracle 11g . here's fiddle link

SELECT name FROM (
  SELECT model.name AS name FROM car  , sale , model
    WHERE car.carid=sale.carid
      AND car.modid=model.modid 
    GROUP BY model.name
    ORDER BY count(*) DESC )
WHERE rownum = 1;

Or

SELECT name FROM (
  SELECT model.name AS name FROM car  natural join sale natural join model    
    GROUP BY model.name
    ORDER BY count(*) DESC )
WHERE rownum = 1;

OUTPUT

|    NAME |
-----------
| Corolla |

Upvotes: 2

jchevali
jchevali

Reputation: 181

When interviewers ask for this they usually want you to say that you'd use windowed functions. You could give each sale a unique ascending number partitioned by model and the highest sale number you'd get would be the max count.

http://www.postgresql.org/docs/9.1/static/tutorial-window.html

Upvotes: 2

John Woo
John Woo

Reputation: 263833

Since you are using SQL Server 2008, make use of Common Table Expression and Window Function.

WITH recordList
AS
(
    SELECT  c.name, COUNT(*) [Count],
            DENSE_RANK() OVER (ORDER BY COUNT(*) DESC) rn
    FROM    Sale a
            INNER JOIN Car b
                ON a.carid = b.carID
            INNER JOIN Model c
                ON b.modID = c.modID
    GROUP   BY c.Name
)
SELECT name, [Count]
FROM recordList
WHERE rn = 1

Upvotes: 3

Related Questions