SQL Queries on Paper

What they ask

The technical round is with the engineering manager, in person, and it is old school: the questions are printed on paper and you write your answers on paper, SQL included. There is no sample data and no schema handed to you, so part of the exercise is stating your assumptions out loud before you write.

The questions are in the SQL area a B2B grocery marketplace cares about, for example:

1. Given retailers(id, city), orders(id, retailer_id, created_at, status, total), and order_items(order_id, product_id, qty, price), return the top 5 retailers by total order value in the last 30 days.
2. List retailers who have placed no order in the last 60 days but had at least one order before that (churn candidates).
3. For each city, the retailer with the second-highest order count.
4. Explain what changes if orders.total is not stored and must be derived from order_items.

They may follow with which index you would add for query 1 and why.

What they look for

• You draw the schema and name your assumptions (timezones, cancelled orders, currency) before writing.
• Correct joins and grouping written by hand, no autocomplete to lean on.
• Comfort with window functions or a clean alternative for ranking.
• Knowing the difference between WHERE and HAVING, and between LEFT JOIN ... IS NULL and NOT EXISTS.
• Being able to say how the query behaves on a large orders table.

The backend stack is Python with Django REST Framework on PostgreSQL, so Postgres-flavoured SQL is welcome.