Business Intelligence Path · Mission 8 of 31Easy

Find reps outselling their department average

Use uncorrelated subqueries in WHERE and SELECT clauses

The Brief

Sandra SanchezVP of Salessales-ops

I want to identify our above-average performers. Pull every active Sales department employee whose total revenue exceeds the average total revenue across all active Sales reps. Show their name, their total revenue, and the department average for context. Sort by revenue descending.

You'll practice

SubqueriesComparison

Tables & columns available

fact_salesfact8 columns

Column	Type	Key
sale_id	INT	PK
employee_id	INT	FK → dim_employees
product_id	INT	FK → dim_products
region_id	INT	FK → dim_regions
sale_date	TEXT
quantity	INT
revenue	REAL
discount	REAL

dim_employeesdim10 columns

Column	Type	Key
employee_id	INT	PK
first_name	TEXT
last_name	TEXT
email	TEXT
department	TEXT
title	TEXT
hire_date	TEXT
salary	INT
manager_id	INT
is_active	INT

Hints (3)

Each hint you reveal reduces the XP you can earn. Try the query first.

Hint 1

This is a two-step problem: first compute each rep's total revenue, then compare each rep to the average across all reps. Think about how to express "the average of per-rep totals" in SQL.

Hint 2

The department average is the average of per-rep totals — use a subquery: SELECT AVG(rep_total) FROM (SELECT SUM(revenue) as rep_total ...)

Hint 3

Compare each rep's total to this average in a HAVING clause or wrap the whole thing in a subquery

← Previous

Mission 7

Mission 9

All 31 missions in Business Intelligence Path ↓