How would you write a SQL query to identify the top 10% of Pinners based on their engagement with promoted pins on Pinterest?
Use all Pinners, including those with no qualifying engagement. Rank by total engagement descending, break ties by pinner_id ascending, and use the ceiling of 10% when the population size does not divide evenly.
pinner_id, username, total_engagements, and engagement_rankengagement_rank ascending, then pinner_id ascending| Column | Type | Description |
|---|---|---|
| pinner_idPK | INT | Unique identifier for a Pinner |
| username | VARCHAR(100) | Pinterest username |
| Column | Type | Description |
|---|---|---|
| promoted_pin_idPK | INT | Unique identifier for a promoted Pin |
| pinner_id | INT | Pinner who owns or published the promoted Pin |
| pin_title | VARCHAR(150) | Short title of the promoted Pin |
| status | VARCHAR(20) | Promotion status |
| Column | Type | Description |
|---|---|---|
| engagement_idPK | INT | Unique identifier for an engagement event |
| promoted_pin_id | INT | Promoted Pin associated with the engagement |
| pinner_id | INT | Pinner who generated the engagement |
| engagement_type | VARCHAR(30) | Type of engagement event |