Data Users Above Their Circle's Average

Medium
509.

Data Users Above Their Circle's Average

Marketing wants to upsell a bigger data pack to subscribers who use more than is typical where they live. A subscriber's August usage is the sum of their mb_used in August 2024; the typical figure for a circle is the average August usage of that circle's subscribers who used any data in August.

Return the subscribers whose August usage is strictly greater than the average for their own circle, with the columns subscriber_id, name, circle and total_mb. Subscribers with no August usage take no part. Return the rows in any order.

Tables

Table: Subscriber

ColumnType
subscriber_idint
namevarchar
circlevarchar

Primary key: subscriber_id.

circle is the subscriber's home circle.

Table: DataUsage

ColumnType
usage_idint
subscriber_idint
usage_datedate
mb_usedint

Primary key: usage_id.

Mobile data used by a subscriber in one session; every subscriber_id is in Subscriber.

Example

The rows may come back in any order; the columns must be the ones shown, in that order.

Subscriber

subscriber_idnamecircle
1AaravKarnataka
2DiyaKarnataka
3IshaanKarnataka
4KavyaKerala
5NehaKerala
6RahulGujarat
7RiyaKarnataka
8SnehaGujarat

DataUsage

usage_idsubscriber_idusage_datemb_used
112024-08-033000
212024-08-201200
322024-08-101500
432024-08-112100
532024-07-309000
642024-08-152000
752024-08-312000
862024-08-01800
972024-09-015000
1082024-08-09300

Output

subscriber_idnamecircletotal_mb
1AaravKarnataka4200
6RahulGujarat800
Read the hints, the editorial and the reference solution
-- Write your MySQL query below

Sign in to write, run and submit queries

A free account — no card, no trial clock. You come straight back here.

Run your query to see its result next to the expected one, example by example.

Data Users Above Their Circle's Average — SQL Problem

Marketing wants to upsell a bigger data pack to subscribers who use more than is typical where they live.

  • Difficulty: Medium
  • Topics: Subqueries, Aggregation
  • Dialect: MySQL
  • Problem: #509

Problem statement

Marketing wants to upsell a bigger data pack to subscribers who use more than is typical where they live. A subscriber's August usage is the sum of their mb_used in August 2024; the typical figure for a circle is the average August usage of that circle's subscribers who used any data in August.

Return the subscribers whose August usage is strictly greater than the average for their own circle, with the columns subscriber_id, name, circle and total_mb. Subscribers with no August usage take no part. Return the rows in any order.

Tables

Table: Subscriber

ColumnType
subscriber_idint
namevarchar
circlevarchar

Primary key: subscriber_id.

circle is the subscriber's home circle.

Table: DataUsage

ColumnType
usage_idint
subscriber_idint
usage_datedate
mb_usedint

Primary key: usage_id.

Mobile data used by a subscriber in one session; every subscriber_id is in Subscriber.

Examples

Example 1

Subscriber

subscriber_idnamecircle
1AaravKarnataka
2DiyaKarnataka
3IshaanKarnataka
4KavyaKerala
5NehaKerala
6RahulGujarat
7RiyaKarnataka
8SnehaGujarat

DataUsage

usage_idsubscriber_idusage_datemb_used
112024-08-033000
212024-08-201200
322024-08-101500
432024-08-112100
532024-07-309000
642024-08-152000
752024-08-312000
862024-08-01800
972024-09-015000
1082024-08-09300

Output

subscriber_idnamecircletotal_mb
1AaravKarnataka4200
6RahulGujarat800

How to solve Data Users Above Their Circle's Average

The comparison is between two aggregates at different levels, so build them in order. The CTE joins subscribers to their August usage — the date range drops July and September rows — and sums per subscriber. Subscribers with no August rows vanish in the inner join, so they are neither returned nor part of any average, as the statement says.

The circle's typical usage is the average of those totals. Averaging the raw usage rows instead would weight a subscriber with ten sessions ten times and give a different number — a common mistake. The correlated subquery (SELECT AVG(o.total_mb) FROM totals o WHERE o.circle = t.circle) computes it per circle, and > is strict, so a circle where everyone used the same amount, or a circle of one, returns nobody.

A window does the same in one level: AVG(SUM(mb_used)) OVER (PARTITION BY circle) averages the grouped sums per circle, and an outer query filters on it. Both read the usage once; the subquery version re-reads the small totals table per row, which is cheap here.

Reference solution (MySQL)

WITH totals AS (
  SELECT s.subscriber_id, s.name, s.circle, SUM(d.mb_used) AS total_mb
  FROM Subscriber s
  JOIN DataUsage d ON d.subscriber_id = s.subscriber_id
  WHERE d.usage_date BETWEEN '2024-08-01' AND '2024-08-31'
  GROUP BY s.subscriber_id, s.name, s.circle
)
SELECT subscriber_id, name, circle, total_mb
FROM totals t
WHERE total_mb > (SELECT AVG(o.total_mb) FROM totals o WHERE o.circle = t.circle)

Another way

SELECT subscriber_id, name, circle, total_mb FROM (
  SELECT s.subscriber_id, s.name, s.circle, SUM(d.mb_used) AS total_mb,
         AVG(SUM(d.mb_used)) OVER (PARTITION BY s.circle) AS circle_avg
  FROM Subscriber s JOIN DataUsage d ON d.subscriber_id = s.subscriber_id
  WHERE d.usage_date >= '2024-08-01' AND d.usage_date < '2024-09-01'
  GROUP BY s.subscriber_id, s.name, s.circle
) x
WHERE total_mb > circle_avg

← Most Recharged Prepaid Plan in Each Circle · Monthly Recharge Revenue by Pack Type →