Salesforce Data Scientist Coding Questions

Salesforce aims to measure the retention rate of its recurring revenue streams effectively. To analyze client financial behavior, we need to use SQL Window Functions to calculate month-over-month retention rates based on client payments.
Problem Statement: Given a table payments with the columns client_id, payment_date, and amount, write a query to compute a retention rate for each client on a month-over-month basis. Consider retention as clients who paid in the current month compared to previous months and return the client_id, month, and retention_rate (as a percentage).
Example 1:
Input:
sql
client_id | payment_date | amount
-----------|--------------|-------
1 | 2021-01-10 | 100
1 | 2021-02-15 | 150
2 | 2021-01-20 | 200
2 | 2021-03-10 | 100
1 | 2021-03-12 | 200

Output:
sql
client_id | month | retention_rate
-----------|-----------|----------------
1 | 2021-01 | NULL
1 | 2021-02 | 100.00
1 | 2021-03 | 50.00
2 | 2021-01 | NULL
2 | 2021-02 | NULL
2 | 2021-03 | 100.00

Constraints:
- There will be valid payment entries for at least one month.
- payment_date will be strictly in the format YYYY-MM-DD.

Using Salesforce's platform, there is a need to analyze customer referral activities over a sliding time window to better understand engagement trends. This tracker will help identify top referrers and tailor rewards accordingly.
Problem Statement: Design a function that takes a list of referrals, where each entry is a tuple containing customer_id, timestamp, and reward_points. The function should return a mapping of customer_id to total reward_points earned within a specified time_window (in seconds).
- Method Signature: def track_referrals(referrals: List[Tuple[str, int, int]], time_window: int) -> Dict[str, int]: — The method returns a dictionary mapping customers to their total reward points within the time window.
Example 1:
Input: referrals = [('A', 1, 10), ('B', 2, 20), ('A', 3, 10), ('A', 4, 20)], time_window = 3
Output: {'A': 20, 'B': 20}
Explanation: Customer 'A' earns points from timestamps 1 to 3 only.
Example 2:
Input: referrals = [('A', 1, 10), ('B', 2, 20), ('A', 5, 30), ('C', 6, 40)], time_window = 5
Output: {'A': 10, 'B': 20, 'C': 40}
Constraints:
- 1 <= len(referrals) <= 10^4
- 0 <= timestamp <= 10^9
- 1 <= reward_points <= 100