Use a CTE to calculate department totals, then find departments contributing more than 30% of total revenue.

Problem Statement

<p>Use a CTE to calculate department totals, then find <code>departments</code> contributing more than 30% of total revenue.</p>

Examples

Input: sales table: +----+------------+--------+ | id | department | amount | +----+------------+--------+ | 1 | IT | 5000 | | 2 | HR | 2000 | | 3 | IT | 3000 | | 4 | Sales | 8000 | | 5 | HR | 1000 | +----+------------+--------+

Output: +------------+------------+-------+ | department | dept_total | pct | +------------+------------+-------+ | Sales | 8000 | 42.11 | | IT | 8000 | 42.11 | +------------+------------+-------+

Explanation: The query retrieves the requested records satisfying all problem requirements.

Complexity

Time Complexity: -

Space Complexity: -

Hints

💡 Hint 1: Identify the grouping dimension(s) and which columns require aggregate functions (such as COUNT, SUM, AVG, MIN, or MAX). 💡 Hint 2: Add the GROUP BY clause for all non-aggregated columns listed in the SELECT projection. 💡 Hint 3: If filtering groups, use HAVING; otherwise use WHERE before grouping: SELECT <group_col>, <AGG>(...) FROM <table> GROUP BY <group_col>;

Editorial & Approach

Problem Overview & Intuition

To solve "CTE with Aggregation", we query the relational database engine using declarative SQL. The goal is to use a cte to calculate department totals, then find departments contributing more than 30% of total revenue. By formulating an optimal execution plan with appropriate projection and filtering, the database engine executes the query with minimal overhead.

Step-by-Step Approach

Analyze Schema: Identify the target tables, necessary foreign keys, and expected output columns.
Construct Filtering & Logic: Apply grouping & aggregates to isolate the requested data.
Format & Order: Ensure columns match the expected project schema in order.

Optimal Implementation (SQL)

WITH dept_totals AS (SELECT department, SUM(amount) AS dept_total FROM sales GROUP BY department), grand_total AS (SELECT SUM(amount) AS total FROM sales) SELECT d.department, d.dept_total, ROUND(d.dept_total * 100.0 / g.total, 2) AS pct FROM dept_totals d, grand_total g WHERE d.dept_total * 100.0 / g.total > 30;

Complexity Analysis

Time Complexity O(N log N) for sorting or partitioning rows.

Space Complexity O(N) for intermediate group hash tables or window buffers.

Key Considerations & Edge Cases

Empty Tables: The query executes safely returning zero rows without syntax error.
NULL Values: Columns containing NULL values are properly handled by standard ANSI SQL semantics.
Case Sensitivity: String comparisons and keywords adhere to PostgreSQL/standard SQL rules.

CTE with Aggregation

Hard

Use a CTE to calculate department totals, then find departments contributing more than 30% of total revenue.

Example Scenarios

1Example 1

Input:

sales table

id	department	amount
1	IT	5000
2	HR	2000
3	IT	3000
4	Sales	8000
5	HR	1000

Output:

department	dept_total	pct
Sales	8000	42.11
IT	8000	42.11

Explanation:

The query retrieves the requested records satisfying all problem requirements.

SQL Editor

Loading Editor...

Query Results

Run a query to see results here.