Top Customer by Orders

You are given the following tables:

transactions table:

id	user_id	created_at	product_id	quantity
integer	integer	datetime	integer	integer

products table:

id	name	price
integer	string	float

users table:

id	name	sex
integer	string	string

Write a query to find the name of the customer who has placed the largest number of orders in total. Your output should have the following column: user_id, name, orders (Total number of orders made by the user)

Community solutions

💬 Community guidelines

Stay on topic. Use this section for submitting solutions and providing feedback to others.
Be inclusive. Exponent is a diverse community. Please respect others' opinions and beliefs.

How would you answer? Submit your response below and get feedback from the community.

Derrick M.

Member

249

July 24, 2024

SQL

SELECT u.id as user_id, u.name,
COUNT(t.product_id) AS orders
FROM users u
JOIN transactions t 
ON
t.user_id = u.id 
JOIN products p 
ON 
p.id = t.product_id
GROUP BY u.id, u.name
ORDER BY orders DESC
LIMIT 1

Aneesha K.50

July 31, 2024

-- Write your query here select user_id, name, COUNT(t.id) AS orders FROM transactions t INNER JOIN users u ON t.user_id = u.id GROUP BY user_id ORDER BY orders desc LIMIT 1 ;

מאיה ט.109

November 10, 2024

select t.user_id, u.name, count(t.id) as orders from transactions t inner join users u on t.user_id=u.id group by 1,2 order by count(t.id) desc limit 1

Ben C.

Member

194

December 11, 2024

The suggested solution is with Joins. It works but it joins two full tables and then filters the result. A more efficient approach is using a subquery or CTE to find the top customer through the transactions table and then get the customer name using join or subquery which will only look for 1 record in the users table.

Most efficient (CTE + JOIN):

SQL

with top_customer as (
    select
        t.user_id,
        count(*) as orders
    from transactions t
    group by t.user_id
    order by orders desc
    limit 1
)
select
    tc.user_id,
    u.name,
    tc.orders
from top_customer tc
join users u
on u.id = tc.user_id

Identifies the top customer first (processing less data)
Then joins with users table only for that one customer
Simple execution plan with minimal data movement

Second query (CTE + Correlated subquery):

SQL

with top_customer as (
    select
        t.user_id,
        count(*) as orders
    from transactions t
    group by t.user_id
    order by orders desc
    limit 1
)
select
    tc.user_id,
    (select name from users where id = tc.user_id) as name,
    tc.orders
from top_customer tc

Similar first step but uses correlated subquery
Correlated subqueries can be less efficient than joins
More complex execution plan

Third query (JOIN first):

SQL

SELECT user_id, name, COUNT(txns.id) orders 
FROM transactions txns 
JOIN users usrs ON txns.user_id = usrs.id 
GROUP BY user_id, name 
ORDER BY COUNT(txns.id) desc 
LIMIT 1

Joins entire transactions and users tables before aggregating
Processes much more data than necessary
Has to group and sort larger dataset

The first query is most efficient because:

It minimizes data processing by finding the top customer first
Only needs to join one record with the users table
Has the simplest and most straightforward execution plan

See community answers

Data Analyst Interviews Introduction

Technical Questions for Data Analysts

Overview

SQL

Excel and Google Sheets

Data Visualization & Dashboarding

Data Analysis Process

Statistics & Experimentation

(Optional) Python Coding

SQL Interviews

Overview

Fast Track

Basic SQL Querying

Aggregations

Joins

Window Functions

Easy Practice Questions

Medium Practice Questions

Hard Practice Questions

Analytical Problem Solving Questions

Overview

Types of Analytical Problem Solving Questions

Framework & Application

Mock Interviews

Take-home Case Studies for Data Analysts

Overview

How to Tackle Take-Home Case Studies

Behavioral Questions for Data Analysts

Overview

Interview Techniques

Mock Interviews

Data Analyst Interviews Introduction

Technical Questions for Data Analysts

Overview

SQL

Excel and Google Sheets

Data Visualization & Dashboarding

Data Analysis Process

Statistics & Experimentation

(Optional) Python Coding

SQL Interviews

Overview

Fast Track

Basic SQL Querying

Aggregations

Joins

Window Functions

Easy Practice Questions

Medium Practice Questions

Hard Practice Questions

Analytical Problem Solving Questions

Overview

Types of Analytical Problem Solving Questions

Framework & Application

Mock Interviews

Take-home Case Studies for Data Analysts

Overview

How to Tackle Take-Home Case Studies

Behavioral Questions for Data Analysts

Overview

Interview Techniques

Mock Interviews

Top Customer by Orders