Connection Invite Acceptance Ratio

Medium

You are given two event tables for a social networking system: friend_request(sender_id, send_to_id, request_date) request_accepted(requester_id, accepter_id, accept_date) Both tables may contain duplicate rows. Definitions: Unique request: distinct pair (sender_id, send_to_id) Unique acceptance: distinct pair (requester_id, accepter_id) Compute one metric: accept_rate = unique_acceptance_count / unique_request_count Rules: Count each directed pair once, even if it appears multiple times. Acceptances are counted even if the corresponding request pair is missing. If unique_request_count is zero, return 0.00. Round the final ratio to 2 decimal places. Return exactly one row with one column: accept_rate Supported submission environments: SQL (SQLite 3.27.2) Pandas (Python)

Example 1

Input

friend_request:
| sender_id | send_to_id | request_date |
|-----------|------------|--------------|
| 1         | 2          | 2024-01-01   |
| 1         | 3          | 2024-01-01   |
| 1         | 4          | 2024-01-01   |
| 2         | 3          | 2024-01-02   |
| 3         | 4          | 2024-01-09   |

request_accepted:
| requester_id | accepter_id | accept_date |
|--------------|-------------|-------------|
| 1            | 2           | 2024-01-03  |
| 1            | 3           | 2024-01-08  |
| 2            | 3           | 2024-01-08  |
| 3            | 4           | 2024-01-09  |
| 3            | 4           | 2024-01-10  |

Output

[
  {"accept_rate":0.8}
]

Explanation

There are 5 unique request pairs and 4 unique acceptance pairs, so the rounded acceptance rate is 0.80.

Example 2

Input

friend_request:
[]

request_accepted:
| requester_id | accepter_id | accept_date |
|--------------|-------------|-------------|
| 9            | 10          | 2024-04-11  |
| 9            | 10          | 2024-04-12  |

Output

[
  {"accept_rate":0.0}
]

Explanation

When no requests exist, denominator is zero and the answer must be 0.00.

Example 3

Input

friend_request:
| sender_id | send_to_id | request_date |
|-----------|------------|--------------|
| 11        | 12         | 2024-02-01   |
| 12        | 13         | 2024-02-02   |
| 13        | 14         | 2024-02-03   |
| 14        | 15         | 2024-02-04   |

request_accepted:
| requester_id | accepter_id | accept_date |
|--------------|-------------|-------------|
| 11           | 12          | 2024-02-05  |
| 12           | 13          | 2024-02-05  |
| 13           | 14          | 2024-02-05  |
| 40           | 41          | 2024-02-06  |
| 41           | 42          | 2024-02-06  |
| 50           | 51          | 2024-02-07  |

Output

[
  {"accept_rate":1.5}
]

Explanation

Accepted pairs can include pairs that never appeared in request events, so the ratio can exceed 1.00.

Constraints

0 <= number of rows in friend_request <= 100000
0 <= number of rows in request_accepted <= 100000
sender_id, send_to_id, requester_id, accepter_id are positive integers
Tables may contain duplicates and have no guaranteed primary key
Directed pairs matter: (u, v) is different from (v, u)
If there are zero unique requests, return 0.00
Round final metric to two decimal places
Use SQLite 3.27.2 semantics for SQL submissions
For Pandas submissions, return a DataFrame with column: accept_rate

friend_request: | sender_id | send_to_id | request_date | |-----------|------------|--------------| | 1 | 2 | 2024-01-01 | | 1 | 3 | 2024-01-01 | | 1 | 4 | 2024-01-01 | | 2 | 3 | 2024-01-02 | | 3 | 4 | 2024-01-09 | request_accepted: | requester_id | accepter_id | accept_date | |--------------|-------------|-------------| | 1 | 2 | 2024-01-03 | | 1 | 3 | 2024-01-08 | | 2 | 3 | 2024-01-08 | | 3 | 4 | 2024-01-09 | | 3 | 4 | 2024-01-10 |

friend_request: [] request_accepted: | requester_id | accepter_id | accept_date | |--------------|-------------|-------------| | 9 | 10 | 2024-04-11 | | 9 | 10 | 2024-04-12 |

friend_request: | sender_id | send_to_id | request_date | |-----------|------------|--------------| | 11 | 12 | 2024-02-01 | | 12 | 13 | 2024-02-02 | | 13 | 14 | 2024-02-03 | | 14 | 15 | 2024-02-04 | request_accepted: | requester_id | accepter_id | accept_date | |--------------|-------------|-------------| | 11 | 12 | 2024-02-05 | | 12 | 13 | 2024-02-05 | | 13 | 14 | 2024-02-05 | | 40 | 41 | 2024-02-06 | | 41 | 42 | 2024-02-06 | | 50 | 51 | 2024-02-07 |