Amazon Data Scientist Coding Questions

To improve customer targeting and marketing strategies, Amazon Fresh wants to analyze shopping trends over a sliding time window.
Problem statement: Write a SQL query to find the average purchase amount of customers over the last 7 days for the given date range. Assume we have a table named purchases with the columns customer_id, purchase_date, and amount.
Example 1:
Input: SELECT ... FROM purchases ... WHERE ... (actual SQL query)
Output: SELECT customer_id, AVG(amount) AS avg_purchase FROM (SELECT customer_id, amount, ROW_NUMBER() OVER (PARTITION BY customer_id ORDER BY purchase_date DESC) AS rn FROM purchases WHERE purchase_date >= DATE_SUB(CURRENT_DATE, INTERVAL 7 DAY)) sub WHERE rn <= 7 GROUP BY customer_id;
Explanation: This query calculates the average amount purchased by each customer for the past 7 days.
Constraints:
- Assume no duplicate purchases occur for a specific customer_id on purchase_date.
- purchase_date is of type DATE and has no NULL values.

To enhance the user experience for Amazon Prime members, we need a dynamic programming solution that optimizes the personalized recommendations based on user purchase history.
Problem statement: Given a list of item_ids a user has purchased and a recommendation_count, determine the maximum number of unique item recommendations based on previous purchases. You should implement the function maxRecommendations(item_ids: List[str], recommendation_count: int) -> int that returns the maximum number of unique items that can be recommended.
Example 1:
Input: maxRecommendations(['A', 'B', 'C', 'A', 'E'], 3)
Output: 3
Explanation: The user can be recommended up to 3 unique items from their purchase history.
Example 2:
Input: maxRecommendations(['A', 'B', 'B', 'C'], 2)
Output: 2
Explanation: The user can be recommended up to 2 unique items from their purchase history.
Constraints:
- 1 <= item_ids.length <= 10^5
- 0 <= recommendation_count <= item_ids.length

Amazon often needs to monitor user sessions in a seamless manner to improve personalization and service recommendations. By keeping track of user activities in a sliding window, we can effectively manage and analyze session data.
Problem statement: You are tasked with implementing a SessionTracker class that maintains session usage data for users based on their active sessions within a specified time frame. The methods you need to implement are:
- start_session(user_id: str, timestamp: int) -> None: Start a session for a given user at the specified timestamp.
- end_session(user_id: str, timestamp: int) -> None: End the session for the user at the specified timestamp.
- get_active_users(current_time: int) -> List[str]: Return a list of users who have active sessions within the last 30 minutes of the current time.
Example 1:
Input: start_session('user1', 100)
Output: None
Explanation: session started for 'user1' at timestamp 100.
Example 2:
Input: start_session('user2', 200)
Output: None
Explanation: session started for 'user2' at timestamp 200.
Constraints:
- 1 <= user_id.length <= 100
- 0 <= timestamp <= 10^9