dbdbexercises
Practice Free
← Syllabus|
Medium

16. Subscription Churn Variance by Billing Frequency

MediumAggregationsPostgreSQL 16PRO
Table: customer_subscriptions
+-------------------+-------------+
| Column Name       | Type        |
+-------------------+-------------+
| sub_id            | int         |
| billing_frequency | varchar(20) |
| status            | varchar(20) |
+-------------------+-------------+

sub_id is the unique key for this table. Each row contains record details for this enterprise dataset.

Subscription financial analysts evaluate churn variance between annual upfront commitments and monthly flexible billing plans to calibrate contract discounting.

Group records in `customer_subscriptions` by `billing_frequency`. Calculate: 1. `billing_frequency` 2. `total_subscriptions`: Total subscriptions 3. `churned_count`: Subscriptions with `status = 'churned'` 4. `churn_rate_pct`: Percentage of subscriptions churned, rounded to 2 decimal places Order results by `churn_rate_pct` descending.

Return the result table ordered as specified. The result format is in the following example.

Example 1:
Input:
Preparing sample rows...
Output:
Calculating benchmark target...
Explanation:

Subscription counts and churn rates by monthly vs annual frequency.

Constraints:
  • Query must utilize clause(s): GROUP BYCOUNTROUNDORDER BY
  • Result order matters (tie-breakers and sort order must be strictly preserved).
  • Engine: PostgreSQL 16 standard syntax (in-browser WASM).
query.sql|PostgreSQL 16 WASM
Loading SQL editor...
You must run your code first

Write your SQL query in the editor and click Run or Submit to test against in-browser PostgreSQL 16 WASM.

About the “Subscription Churn Variance by Billing Frequency” SQL Problem

Business Scenario

Subscription financial analysts evaluate churn variance between annual upfront commitments and monthly flexible billing plans to calibrate contract discounting.

Expected Output & Criteria

Subscription counts and churn rates by monthly vs annual frequency.

Database Table Schema (PostgreSQL DDL)

PostgreSQL 16 WASM
CREATE TABLE customer_subscriptions (
        sub_id INT PRIMARY KEY,
        billing_frequency VARCHAR(20) NOT NULL,
        status VARCHAR(20) NOT NULL
      );

Target SQL Clauses & Operators

GROUP BYCOUNTROUNDORDER BY

Want more Aggregations practice?

Explore the full syllabus of 16 exercises and syntax guides.

View Aggregations Track