Sign In Pages - Count Unique Users with Successful Auth per Landing Page

Question

## Problem

Given a table of authentication events:

```sql
CREATE TABLE auth_events (
    user_id       BIGINT,
    session_id    VARCHAR(36),
    landing_page  VARCHAR(200),
    event_type    VARCHAR(20),  -- 'attempt' or 'success'
    event_time    TIMESTAMP
);
```

For each `landing_page`, return the number of distinct users who had at least one `success` event, and the success rate (successes / total attempts) rounded to 4 decimal places. Order by success rate descending.

## Example

```
landing_page | unique_success_users | success_rate
/login/oauth | 840                  | 0.9123
/login/email | 620                  | 0.7845
/login/sso   | 210                  | 0.6700
```

## Follow-ups

1. A user may attempt from multiple sessions on the same page -- how do you avoid double-counting in unique user count vs. attempt count?
2. Add a time filter: only count events within the past 7 days.
3. Identify landing pages with a significant drop in success rate compared to last week (> 10% relative drop).
4. How do you index `auth_events` for this query pattern?

Sign In Pages - Count Unique Users with Successful Auth per Landing Page

Question Details

Full Details

Topics

More from Amazon