Google Query: filter one record per day/week per user

Question

I'm collecting repeated measures using Google Forms which only allows 1 or unlimited responses. I need a way to filter out responses that were entered by the same user too near to each other.

For example, if the below is the raw form responses, I'd want the query to exclude the final row since Jim had already responded on that day (or alternately keep his second response and exclude his first on 12/12).

| TimeStamp            | UserID | SurveyData |
|----------------------|--------|------------|
| 12/03/2020 16:10:40  | Bob    | Dog        |
| 12/03/2020 16:10:20  | Sue    | Cat        |
| 12/03/2020 16:50:40  | Jim    | Dog        |
| 12/15/2020 16:10:40  | Bob    | Cat        |
| 12/14/2020 16:10:20  | Sue    | Dog        |
| 12/12/2020 16:50:40  | Jim    | Cat        |
| 12/12/2020 20:00:00  | Jim    | Bird       |

Should support 2000 unique users, each typically adding one record each month. Ideally, this could be written as a single query or filter but could use helper col counting number of entries for user on that day if that's easier. Would also be helpful if the exclusion criteria could be easily adjusted (e.g. changed to allow one entry per calendar week).

Answer 1

To limit to 1 per week: Create a helper column D that calculates the week of the year:

=ISOWEEKNUM( A2 )

      A                   B           C          D
| TimeStamp            | UserID | SurveyData | Week
|----------------------|--------|------------|------
| 12/03/2020 16:10:40  | Bob    | Dog        |11
| 12/03/2020 16:10:20  | Sue    | Cat        |11
| 12/03/2020 16:50:40  | Jim    | Dog        |11
| 16/03/2020 16:10:40  | Bob    | Cat        |12
| 16/03/2020 16:10:20  | Sue    | Dog        |11
| 16/03/2020 16:50:40  | Jim    | Cat        |12
| 17/03/2020 20:00:00  | Jim    | Bird       |12

Use to SORTN filter out any duplicates by the same person occurring in the same week. (In this case, only dropping the last row.)

=SORTN(A2:D,9^9,2,COLUMN(B1),1,COLUMN(D1)

Explanation: SORTN (range, max number of results to return (a big #), tiebreaker type (remove duplicates), First column to sort by, sort order, second column to sort by if first was a tie, sort order.