DB Exercises
0
← Exercises/
CTEs•5 of 12
PrevNext
CTEsAdvancedPRO

High Net Worth Liquidity Segmentation

Scenario

Private wealth management routes clients to dedicated advisors based on total aggregated liquidity across checking, savings, and investment accounts.

Task
1. Write a CTE `client_balances` that computes the total balance for each user across all accounts in `financial_accounts`. 2. In the outer query, categorize clients into wealth tiers: - `>= 250000.00` -> `'Private Client'` - `>= 100000.00` -> `'Premier'` - ELSE -> `'Standard'` 3. Group by `wealth_tier`, counting how many clients fall in each tier and the total assets in that tier. Return: 1. `wealth_tier` 2. `client_count` 3. `total_tier_assets` Order results by `total_tier_assets` descending.
Expected Output Format

Summary of client count and total assets grouped by wealth tier.

Input Tables & Sample Data0 tables
Loading database schema...
Need a hint?
query.sql|Local Sandbox
Loading SQL editor...
Click "Run" or press ⌘ + Enter to execute your query

About the “High Net Worth Liquidity Segmentation” SQL Problem

Business Scenario

Private wealth management routes clients to dedicated advisors based on total aggregated liquidity across checking, savings, and investment accounts.

Expected Output & Criteria

Summary of client count and total assets grouped by wealth tier.

Database Table Schema (PostgreSQL DDL)

PostgreSQL 16 WASM

This challenge executes directly against the following relational schema in your browser:

CREATE TABLE financial_accounts (
        account_id INT PRIMARY KEY,
        user_id INT NOT NULL,
        account_type VARCHAR(30) NOT NULL,
        balance_usd NUMERIC(12, 2) NOT NULL
      );

Target SQL Clauses & Operators

WITHGROUP BYCASE

Want more CTEs practice?

Explore the full syllabus of 12 exercises, syntax guides, and interview pitfalls.

View CTEs Track→