dbdbexercises
Practice Free
← Syllabus|
Medium

18. Dense Ranking of Workspaces by License Seat Saturation

MediumWindow FunctionsPostgreSQL 16PRO
Table: saas_workspaces
+-----------------+--------------+
| Column Name     | Type         |
+-----------------+--------------+
| workspace_id    | varchar(16)  |
| org_name        | varchar(100) |
| plan_tier       | varchar(20)  |
| active_seats    | int          |
| allocated_seats | int          |
+-----------------+--------------+

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

Customer Success teams identify enterprise accounts ripe for expansion by ranking workspaces within each subscription tier based on their active user seat utilization percentage.

For each workspace in `saas_workspaces`, calculate the seat utilization percentage as `ROUND(active_seats * 100.0 / allocated_seats, 1)`. Assign `saturation_rank` using `DENSE_RANK()` partitioned by `plan_tier` and ordered by utilization percentage descending. Return: 1. `workspace_id` 2. `org_name` 3. `plan_tier` 4. `utilization_pct` 5. `saturation_rank` Order results by `plan_tier` ascending, then by `saturation_rank` ascending, then by `workspace_id` ascending.

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:

Dense rank of SaaS accounts by license saturation within tier.

Constraints:
  • Query must utilize clause(s): DENSE_RANKOVERPARTITION 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 “Dense Ranking of Workspaces by License Seat Saturation” SQL Problem

Business Scenario

Customer Success teams identify enterprise accounts ripe for expansion by ranking workspaces within each subscription tier based on their active user seat utilization percentage.

Expected Output & Criteria

Dense rank of SaaS accounts by license saturation within tier.

Database Table Schema (PostgreSQL DDL)

PostgreSQL 16 WASM
CREATE TABLE saas_workspaces (
        workspace_id VARCHAR(16) PRIMARY KEY,
        org_name VARCHAR(100) NOT NULL,
        plan_tier VARCHAR(20) NOT NULL,
        active_seats INT NOT NULL,
        allocated_seats INT NOT NULL
      );

Target SQL Clauses & Operators

DENSE_RANKOVERPARTITION BY

Want more Window Functions practice?

Explore the full syllabus of 18 exercises and syntax guides.

View Window Functions Track