Interview Question Bank · GROUP BY + RANK · mode with ties
4. Each customer's favourite cuisine
MediumFood deliveryQuestion #232
Concept: GROUP BY + RANK · mode with tiesshow
The mode is the most common value in a group. Count per (person, value), rank those counts with RANK() OVER (PARTITION BY person ORDER BY COUNT(*) DESC), and keep rank 1. RANK gives tied values the same rank, so a tie returns every winner, while ROW_NUMBER quietly keeps an arbitrary one.
Example (different data):
WITH g AS (
SELECT l.reader_id, b.genre, COUNT(*) AS loans,
RANK() OVER (PARTITION BY l.reader_id ORDER BY COUNT(*) DESC) AS rk
FROM loans l JOIN books b ON b.id = l.book_id
GROUP BY l.reader_id, b.genre
)
SELECT reader_id, genre, loans FROM g WHERE rk = 1;Your task
Brightbowl's CRM team wants to personalise each customer's home screen with their favourite cuisine, based on the orders that actually reached them.
Using delivered orders only, find each customer's most-ordered cuisine (the restaurant's cuisine). If several cuisines tie for the top, return all of them. Return customer (the customer's name), cuisine and orders (delivered orders of that cuisine). Sort by customer, then cuisine.
Starting Postgres in your browser…
Checking your access…