Count Members Flagged for Three Violations in 90 Days, Overall and by Violation Type
Company: LinkedIn
Role: Data Scientist
Category: Data Manipulation (SQL/Python)
Difficulty: easy
Interview Round: Technical Screen
A trust-and-safety team logs every harmful-content violation committed by a member. It is comparing two rules for flagging repeat violators and wants to know how many members each rule would flag.
1. **Rule 1, any type:** flag a member who has three violations within 90 days.
2. **Rule 2, same type:** flag a member who has three violations of the same `violation_type` within 90 days. Violations of other types that fall between them do not matter.
Three violations are within 90 days when the latest of their dates is at most 90 days after the earliest, counting whole calendar days. For example, violations on 2025-01-01, 2025-03-01 and 2025-04-01 qualify, because 2025-04-01 is exactly 90 days after 2025-01-01; a third violation on 2025-04-02 (91 days later) would not. Any three of a member's violations can satisfy a rule; the window does not have to start at the member's first violation.
The report describes rule 1 as three violations in a row within 90 days and asks for the number of flagged members under each rule, with rule 2 posed as a follow-up on the same table after a violation-type column is added. This version includes the type column from the start, has rule 1 ignore it, and fixes the 90-day boundary as stated above.
### Input Table(s)
**`member_violations`**: one row per violation.
| Column | Type | Meaning |
| --- | --- | --- |
| `violation_id` | INTEGER | Primary key; unique per violation |
| `member_id` | INTEGER | The member who committed the violation |
| `violation_date` | DATE | Calendar date of the violation |
| `violation_type` | TEXT | Violation category, such as `harassment` or `nudity` |
### Output Contract
Write one read-only PostgreSQL query (`SELECT`, optionally with `WITH`) that returns exactly one row with these columns, in this order:
| Column | Type | Meaning |
| --- | --- | --- |
| `flagged_any_type` | integer | Number of distinct members flagged by rule 1 |
| `flagged_same_type` | integer | Number of distinct members flagged by rule 2 |
The result always has exactly one row, so no `ORDER BY` is needed. A count is `0`, never NULL, when no member qualifies, including when the table is empty.
### Example
`member_violations`:
| violation_id | member_id | violation_date | violation_type |
| --- | --- | --- | --- |
| 1 | 1 | 2025-01-01 | harassment |
| 2 | 1 | 2025-03-01 | harassment |
| 3 | 2 | 2025-02-04 | nudity |
| 4 | 3 | 2025-05-28 | harassment |
| 5 | 1 | 2025-04-01 | nudity |
| 6 | 2 | 2025-02-20 | nudity |
| 7 | 2 | 2025-06-15 | nudity |
| 8 | 3 | 2025-06-01 | harassment |
| 9 | 3 | 2025-06-10 | harassment |
| 10 | 4 | 2025-01-10 | harassment |
| 11 | 4 | 2025-02-10 | nudity |
| 12 | 4 | 2025-04-15 | harassment |
| 13 | 4 | 2025-04-20 | nudity |
| 14 | 5 | 2025-03-01 | harassment |
| 15 | 5 | 2025-03-05 | nudity |
| 16 | 5 | 2025-03-20 | harassment |
| 17 | 5 | 2025-05-25 | harassment |
| 18 | 6 | 2025-01-01 | harassment |
| 19 | 6 | 2025-02-15 | harassment |
| 20 | 6 | 2025-04-02 | harassment |
Expected result:
| flagged_any_type | flagged_same_type |
| --- | --- |
| 4 | 2 |
- Member 1: the violations on 2025-01-01, 2025-03-01 and 2025-04-01 span exactly 90 days, so rule 1 flags the member. Only two are harassment, so rule 2 does not.
- Member 2: three nudity violations, but 2025-06-15 is 131 days after 2025-02-04, so neither rule flags the member.
- Member 3: three harassment violations within 13 days, so both rules flag the member.
- Member 4: the first three violations span 95 days, but the last three (2025-02-10 to 2025-04-20) span 69 days, so rule 1 flags the member. Each type has only two violations, so rule 2 does not.
- Member 5: the violations on 2025-03-01, 2025-03-05 and 2025-03-20 span 19 days, so rule 1 flags the member. The harassment violations on 2025-03-01, 2025-03-20 and 2025-05-25 span 85 days, and the nudity violation between them does not matter, so rule 2 flags the member too.
- Member 6: three harassment violations, but 2025-04-02 is 91 days after 2025-01-01, so neither rule flags the member.
Rule 1 flags members 1, 3, 4 and 5; rule 2 flags members 3 and 5.
### Constraints and Clarifications
- No column contains NULL, and there are no duplicate rows: `violation_id` is unique.
- A member may have several violations on the same date. Each one counts separately, so three violations on one date are three violations within 90 days, and they satisfy rule 2 if they share a type.
- Rule 1 ignores `violation_type` entirely. Rule 2 compares `violation_type` values exactly as stored.
- Each member is counted at most once per rule, however many qualifying sets of three violations they have.
- Members with no violations do not appear in the table and are not counted.
- Dates can span several years; day differences are actual calendar-day differences, so month lengths and leap years follow from date arithmetic.
```hint The window can start anywhere
A member's first violation is not special. Think about which of the member's violations could be the last of the three.
```
Overview: A LinkedIn data science SQL question on a log of member harmful-content violations: count the members flagged by a rule of three violations within 90 days, and by a stricter rule requiring three violations of the same type within 90 days. It tests sliding date windows per member, an inclusive day boundary and per-type grouping.
Read the full LinkedIn Data Scientist interview experience this question came from