Spark Augmented Reality (AR) Filter Engagement Metrics

[Lomanu4]

I recently completed an SQL challenge on the interviewmaster.ai platform involving a scenario where I am the data analyst in the marketing analytics team at Meta and have been tasked with evaluating the performance of branded AR filters with the aim of identifying which filters are driving the highest user interactions and shares to inform future campaign strategies for brands using the Spark AR platform. By analyzing engagement data, my team aims to provide actionable insights that will enhance campaign effectiveness and audience targeting.
I completed this challenge using SQLite.

I was provided with 2 tables:

1. ar_filters: containing the filter_id and the filter_name fields
2. ar_filters_engagements containing the engagement_id, filter_id, interaction_count and engagement_date fields

Challenge 1

I was required to query the dataset to return the AR filters that have generated (at least 1) user interactions in July 2024 by their filter names.

This challenge required me to;

1. Retrieve the filter names.
2. Use the SUM() aggregate function.
3. Join the two tables using the filter_id as the common field in both tables.
4. Filter the result based on the required date using the strftime() function.
5. Order the result by the total interaction count.

SELECT f.filter_id,
f.filter_name,
SUM(e.interaction_count) AS total_interaction_count
FROM ar_filters AS f
JOIN ar_filter_engagements AS e
ON f.filter_id = e.filter_id
WHERE strftime('%Y', e.engagement_date) = '2024'
AND strftime('%m', e.engagement_date) = '07'
GROUP BY f.filter_id, f.filter_name
ORDER BY total_interaction_count DESC

Пожалуйста Авторизируйтесь или Зарегистрируйтесь для просмотра скрытого текста.

Challenge 2

I was required to get how many total interactions each AR filter received in August 2024, and to return only filter names that received over 1000 interactions, and their respective interaction counts.

This challenge required me to use the HAVING clause to selectively filter out only the AR filters that have more than 1000 engagements.

Although, this seems like a filter function that could have been done using the WHERE statement, SQL does not support using an aggregate function SUM(e.interaction_count) in this case, hence the reason why we had to use the HAVING clause.


SELECT f.filter_id,
f.filter_name,
SUM(e.interaction_count) AS total_interaction_count
FROM ar_filters AS f
JOIN ar_filter_engagements AS e
ON f.filter_id = e.filter_id
WHERE strftime('%Y', e.engagement_date) = '2024'
AND strftime('%m', e.engagement_date) = '08'
GROUP BY f.filter_id, f.filter_name
HAVING SUM(e.interaction_count) > 1000
ORDER BY total_interaction_count DESC

Пожалуйста Авторизируйтесь или Зарегистрируйтесь для просмотра скрытого текста.

Challenge 3

In the third and last challenge, I was required to write a query that returns the top 3 AR filters with the highest number of interactions in September 2024 and show how many interactions each filter received.

All I had to do was to edit the query that solved the second task. I removed the HAVING clause (since this challenge did not require an engagement count limit to be added to the result), and LIMITed my answer to just the top 3 filter names.


SELECT f.filter_id,
f.filter_name,
SUM(e.interaction_count) AS total_interaction_count
FROM ar_filters AS f
JOIN ar_filter_engagements AS e
ON f.filter_id = e.filter_id
WHERE strftime('%Y', e.engagement_date) = '2024'
AND strftime('%m', e.engagement_date) = '09'
GROUP BY f.filter_id, f.filter_name
ORDER BY total_interaction_count DESC
LIMIT 3

Пожалуйста Авторизируйтесь или Зарегистрируйтесь для просмотра скрытого текста.

Overall, it was a thrilling challenge which required some serious analytical thinking.

What do you think about it? Recommendations are highly welcome.

Пожалуйста Авторизируйтесь или Зарегистрируйтесь для просмотра скрытого текста.