Third Largest SQL Query | Dataford Interview Questions

Your question is Third Largest SQL Query. Start with the requirements and the two tables on the right.

Run and submit as often as you like. When you're ready, talk me through your approach or go straight to the code.

Problem

Airtel Digital uses Airtel Thanks transaction data to analyze successful mobile data-pack purchases. Write a PostgreSQL query to find the third-largest distinct purchase amount and return every matching transaction with the associated customer name.

Requirements

Consider only transactions where product_type = 'DATA' and status = 'SUCCESS'.
Exclude NULL amounts and transactions whose customer does not exist in customers.
Rank distinct amounts from highest to lowest using a window function, then return all transactions at rank 3.
Order the output by amount descending and transaction ID ascending.

Schema

customers

Column	Type	Description
customer_idPK	INTEGER	Unique customer identifier
customer_name	VARCHAR(100)	Customer's full name
city	VARCHAR(50)	Customer's registered city

transactions

Column	Type	Description
transaction_idPK	INTEGER	Unique transaction identifier
customer_id	INTEGER	References customers.customer_id
product_type	VARCHAR(20)	Type of Airtel product purchased
status	VARCHAR(20)	Transaction processing status
amount	NUMERIC(10,2)	Purchase amount in Indian rupees