Write a SQL query to calculate the month-over-month retention rate of users who interacted with an AR lens on Snapchat.
Assume retention for a month means the percentage of users active in the previous month who were also active in the current month. Include only valid users and lenses identified as AR lenses.
month, active_users, retained_users, and retention_rateactive_users is the prior month's user count, and retained_users is the count active in both monthsretention_rate as a percentage rounded to two decimals, ordered chronologically| Column | Type | Description |
|---|---|---|
| user_idPK | INT | Unique Snapchat user identifier |
| username | VARCHAR(50) | User handle |
| platform | VARCHAR(20) | Primary mobile platform |
| signup_date | DATE | Date the user registered |
| Column | Type | Description |
|---|---|---|
| lens_idPK | INT | Unique lens identifier |
| lens_name | VARCHAR(100) | Display name of the lens |
| lens_type | VARCHAR(30) | Lens classification |
| is_ar_lens | BOOLEAN | Whether the lens is an AR lens |
| Column | Type | Description |
|---|---|---|
| interaction_idPK | INT | Unique interaction identifier |
| user_id | INT | User who interacted with the lens |
| lens_id | INT | Lens used during the interaction |
| interacted_at | TIMESTAMP | Timestamp of the lens interaction |
| session_id | VARCHAR(40) | Optional Snapchat session identifier |