Top Shared Lenses by Region | Dataford Interview Questions

Snapchat wants to identify the most frequently shared Lenses in each user region during the most recent completed seven-day reporting period. Write a SQL query to calculate Lens share counts by region and return the top three Lenses for every region.

Output

One row per qualifying region and Lens, with columns region, lens_id, lens_name, and share_count.
Include shares from September 8, 2026 through September 14, 2026, excluding September 15, 2026 and excluding rows whose user region is NULL.
Return up to three Lenses per region, ordered by descending share_count, with ascending lens_id as the deterministic tie-breaker.
Order the final results by region, descending share_count, and ascending lens_id.

Schema

users

Column	Type	Description
user_idPK	INT	Unique Snapchat user identifier
region	VARCHAR(50)	Geographic region associated with the user

lenses

Column	Type	Description
lens_idPK	INT	Unique Snapchat Lens identifier
lens_name	VARCHAR(100)	Display name of the Lens

lens_shares

Column	Type	Description
share_idPK	INT	Unique Lens share event identifier
user_id	INT	User who shared the Lens
lens_id	INT	Lens that was shared
shared_at	DATE	Date on which the Lens was shared

Your question is Top Shared Lenses by Region. Start with the requirements and the three tables in the Question tab.

Run and submit as often as you like. When you're ready, talk me through your approach or go straight to the code.

Problem