SQL Quest › SQL Interview Questions › String Functions

Email Provider Customer Analysis

MediumFreeQuerying BasicsString FunctionsAggregation & GroupingConditional Logic

Group customers by their email domain (part after @). For each domain show: domain, customer_count, gold_members (count with membership = 'Gold'), avg_total_orders (average of total_orders column, 1 dec). Sort by customer_count descending.

Solve it in the browser editor →

Runs on SQLite in your browser, graded against the expected result, no signup. A wrong answer gets a diagnosis, not just "incorrect".

Schema

customers

customer_id	name	email	signup_date	membership	total_orders
1	John Smith	john.smith@email.com	2023-01-15	Gold	15
2	Emma Wilson	emma.wilson@email.com	2023-03-20	Silver	8
3	Michael Brown	michael.brown@email.com	2023-02-10	Gold	12

Expected output: domain='gmail.com' with customer metrics

Hint

SUBSTR(email, INSTR(email, '@') + 1) extracts the domain. GROUP BY domain, then use SUM(CASE WHEN membership = 'Gold' THEN 1 ELSE 0 END) for gold count

Concepts

SELECT String Functions GROUP BY Aggregation ORDER BY CASE

Practise the topic: SQL practice questions · String function practice · GROUP BY exercises · CASE WHEN practice

In these company practice sets

Google · Snowflake

A SQL Quest challenge matched to patterns reported for these companies — not a question any of them has published.

Email Provider Customer Analysis

Schema

Hint

Concepts

In these company practice sets

Related questions