SQL Query Construction

Viasat tracks satellite internet usage per subscriber to enforce data caps. A software engineer is reviewing the query below, written to flag subscribers who've gone over their plan's cap this billing cycle, ahead of adding it to a nightly job. The data_usage table holds roughly 6 billion rows across all terminals and only has a primary key index today.

CREATE TABLE subscribers (
    subscriber_id BIGINT PRIMARY KEY,
    plan_id         INT,
    signup_date      DATE
);

CREATE TABLE service_plans (
    plan_id       INT PRIMARY KEY,
    plan_name     TEXT,
    data_cap_gb     INT
);

-- data_usage: ~6 billion rows, one per data session, only usage_id is indexed
CREATE TABLE data_usage (
    usage_id       BIGINT PRIMARY KEY,
    subscriber_id  BIGINT REFERENCES subscribers(subscriber_id),
    session_start  TIMESTAMP,
    bytes_used     BIGINT
);

SELECT
    s.subscriber_id,
    sp.plan_name,
    (SELECT SUM(du.bytes_used) FROM data_usage du
       WHERE du.subscriber_id = s.subscriber_id
         AND du.session_start >= '2024-09-01') / 1073741824.0 AS gb_used,
    sp.data_cap_gb
FROM subscribers s
JOIN service_plans sp ON sp.plan_id = s.plan_id
WHERE (SELECT SUM(du2.bytes_used) FROM data_usage du2
         WHERE du2.subscriber_id = s.subscriber_id
           AND du2.session_start >= '2024-09-01') > sp.data_cap_gb * 1073741824
ORDER BY gb_used DESC;

How would you write this SQL query, and what is the size and columns of the table that make it slow?

Output

1. Return one row per subscriber whose usage exceeds the plan cap, with subscriber_id, plan_name, gb_used, and data_cap_gb.
2. Ignore rows without a matching subscriber, plan, usage subscriber, session timestamp, or plan cap.
3. Order by gb_used descending, then subscriber_id ascending.