SignPost Interview Question
Software Engineer / DevelopersCountry: United States
SELECT rid.name, cnt.votes
FROM rides AS rid
INNER JOIN
(
SELECT favorite_ride_id, count(1) AS votes
FROM persons
GROUP BY favorite_rid_id
) AS cnt
ON cnt.favorite_ride_id = rid.id
ORDER BY cnt.votes DESC
SELECT persons.first_name, persons.last_name, rides.name
FROM persons
INNER JOIN rides
ON rides.id = persons.favorite_ride_id
INNER JOIN
(
SELECT persons.favorite_ride_id, COUNT(*) AS num_votes
FROM persons
GROUP BY persons.favorite_ride_id
ORDER BY num_votes DESC
LIMIT 10
) AS top
ON top.favorite_ride_id = persons.favorite_ride_id
ORDER BY top.num_votes DESC;
sel r.name,count(*) as votes from rides r
left join persons p on
r.id=p.favorite_ride_id group by votes order by votes desc limit 10
Pls correct me if i m wrong
select rides.name, count(*) as votes from persons left join rides on persons.favorite_ride_id = rides.id group by persons.favorite_ride_id order by votes limit 10;
- afk September 19, 2012