SQL for Customer and Repeat Orders

HardSQL · PostgreSQL

Problem

Swiggy's Business Analytics team wants a combined SQL report covering IPL fixture combinations, restaurant customer exclusions, and monthly retention. Write one PostgreSQL query that returns all three analyses in a consistent output format.

Requirements

Generate every unique IPL team pairing so each team plays every other team once. Do not pair a team with itself or duplicate a pairing.
Find customers who ordered from KFC or McDonald's at least once, but never ordered from Burger King. Return their customer details.
Calculate monthly repeat order rate as customers who ordered in both the current and immediately previous calendar month divided by all customers ordering in the current month. Include active customers, repeat customers, and the rate.
Label each result type so the combined output can be separated downstream. Round the rate to four decimal places.

Schema

teams

Column	Type	Description
team_idPK	INT	IPL team identifier
team_name	VARCHAR(50)	IPL team name

customers

Column	Type	Description
customer_idPK	INT	Swiggy customer identifier
customer_name	VARCHAR(100)	Customer name
city	VARCHAR(50)	Customer city

restaurants

Column	Type	Description
restaurant_idPK	INT	Restaurant identifier
restaurant_name	VARCHAR(100)	Restaurant brand

orders

Column	Type	Description
order_idPK	INT	Order identifier
customer_id	INT	Ordering customer
restaurant_id	INT	Ordered restaurant
ordered_at	DATE	Order date

Tablesteamscustomersrestaurantsorders