SQL Query for 3rd Largest

MediumSQL · PostgreSQL

Problem

The Airtel Thanks analytics team wants to identify subscribers with the third-highest total mobile data consumption for January 2026. Write a PostgreSQL query that aggregates usage for active subscribers and ranks distinct usage totals.

Requirements

Join subscribers, active subscriptions, Airtel plans, and usage sessions.
Sum each active subscriber's January 2026 data usage in MB, treating subscribers with no usage as zero.
Return every subscriber tied at the third-highest distinct usage total. Use a window function rather than LIMIT and OFFSET.
Return customer_id, customer_name, plan_name, and total_data_mb, ordered by customer_id.

Schema

customers

Column	Type	Description
customer_idPK	INT	Unique Airtel subscriber identifier
customer_name	VARCHAR(100)	Subscriber display name
account_status	VARCHAR(20)	Current account state

subscriptions

Column	Type	Description
subscription_idPK	INT	Unique subscription identifier
customer_id	INT	References customers.customer_id
plan_id	INT	References plans.plan_id
subscription_status	VARCHAR(20)	Current subscription state

plans

Column	Type	Description
plan_idPK	INT	Unique Airtel plan identifier
plan_name	VARCHAR(100)	Commercial name of the Airtel plan

usage_sessions

Column	Type	Description
session_idPK	INT	Unique data usage session identifier
customer_id	INT	References customers.customer_id
data_mb	INT	Data consumed during the session in MB
usage_date	DATE	Date on which the data was consumed
usage_type	VARCHAR(30)	Network usage classification

Tablescustomerssubscriptionsplansusage_sessions