Drill 4 of 12 in Food delivery questions
0

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…