Interview Guides

Track User Activity Across Multiple Platforms

Hard

SQL & Data Manipulation

Task

A digital marketing agency needs to analyze user activity across multiple platforms (web and mobile) to ensure data accuracy. Write a SQL query to calculate the total activity count for each user, along with their last activity date, while ensuring that data from both platforms is accurately represented.

Requirements

Join the users, web_activity, and mobile_activity tables.
Sum the total activities from both platforms for each user.
Identify the last activity date for each user.
Return results ordered by total activity count in descending order.

Schema

users (id, name) web_activity (user_id, activity_date) mobile_activity (user_id, activity_date)

users	web_activity	mobile_activity
id	user_id	user_id
-------	--------------	-----------------
INT	INT	INT
1	1	1
2	2	2
3	3	NULL

Expected Output

user_id	total_activity	last_activity_date
1	5	2024-01-05
2	3	2024-01-03
3	1	2024-01-01

Track User Activity Across Multiple Platforms

Hard

SQL & Data Manipulation

Task

Requirements

Join the users, web_activity, and mobile_activity tables.
Sum the total activities from both platforms for each user.
Identify the last activity date for each user.
Return results ordered by total activity count in descending order.

Schema

users (id, name) web_activity (user_id, activity_date) mobile_activity (user_id, activity_date)

users	web_activity	mobile_activity
id	user_id	user_id
-------	--------------	-----------------
INT	INT	INT
1	1	1
2	2	2
3	3	NULL

Expected Output

user_id	total_activity	last_activity_date
1	5	2024-01-05
2	3	2024-01-03
3	1	2024-01-01

Practice SQL

PostgreSQL

Track User Activity Across Multiple Platforms

Hard

SQL & Data Manipulation

Task

Requirements

Join the users, web_activity, and mobile_activity tables.
Sum the total activities from both platforms for each user.
Identify the last activity date for each user.
Return results ordered by total activity count in descending order.

Schema

users (id, name) web_activity (user_id, activity_date) mobile_activity (user_id, activity_date)

users	web_activity	mobile_activity
id	user_id	user_id
-------	--------------	-----------------
INT	INT	INT
1	1	1
2	2	2
3	3	NULL

Expected Output

user_id	total_activity	last_activity_date
1	5	2024-01-05
2	3	2024-01-03
3	1	2024-01-01

Track User Activity Across Multiple Platforms

Hard

SQL & Data Manipulation

Task

Requirements

Join the users, web_activity, and mobile_activity tables.
Sum the total activities from both platforms for each user.
Identify the last activity date for each user.
Return results ordered by total activity count in descending order.

Schema

users (id, name) web_activity (user_id, activity_date) mobile_activity (user_id, activity_date)

users	web_activity	mobile_activity
id	user_id	user_id
-------	--------------	-----------------
INT	INT	INT
1	1	1
2	2	2
3	3	NULL

Expected Output

user_id	total_activity	last_activity_date
1	5	2024-01-05
2	3	2024-01-03
3	1	2024-01-01

Practice SQL

PostgreSQL

Track User Activity Across Multiple Platforms | Dataford Interview Questions - Dataford - Ace your Interview