← Airwallex Interview Insights
First, clarify the table schema and the definition of 'last 7 days' (e.g., relative to current date or max date in data). Then, aggregate total views per post over the last 7 days, filter for posts with at least 10 views, and count the distinct posts.
Pro tip: Mention that you would confirm whether the view counts are already aggregated per user-post-day or if they need to be summed; also discuss handling of time zones and date boundaries to ensure accurate 7-day window.
Ask about the table structure, column names, and how 'last 7 days' is defined (e.g., relative to current date or max date in the table). Confirm that view counts are additive and that a post is identified by a unique post_id.
Use a WHERE clause to restrict rows to the last 7 days based on the date column, ensuring the window is correctly calculated (e.g., date >= CURRENT_DATE - INTERVAL '7 days' or using a subquery for max date).
Group by post_id and sum the view counts to get total views per post over the 7-day period.
Apply a HAVING clause to keep only posts where the total views are at least 10.
Wrap the filtered result in a subquery or use COUNT(DISTINCT post_id) to get the final count of distinct posts.
AI-generated suggestions, not part of the candidate's original notes. May be inaccurate — verify before relying on them.
First, clarify the table schemas and join keys between the policy scores table and the views table. Then, filter views to the last 30 days, join with policy scores, and classify posts as Nudity violations if the probability exceeds 0.5. Finally, compute the sum of views for those posts and divide by the total views in the same period to get the share.
Pro tip: Always confirm the time zone and whether 'last 30 days' is inclusive of today; also check for duplicate policy scores per post and decide on an aggregation method (e.g., max or latest).
Identify the relevant tables: one with per-post violation probabilities (policy scores) and one with post views. Determine the join key (e.g., post_id) and ensure you know the time column for views.
Apply a date filter to the views table to include only views from the last 30 days relative to the current date. Be mindful of time zones and whether to include the current day.
Join the filtered views with the policy scores table on post_id. Classify each post as a Nudity violation if its nudity probability > 0.5. Handle any duplicate scores per post (e.g., by taking the latest or max).
Calculate the sum of views for posts classified as Nudity violations and divide it by the total sum of views in the last 30 days. Multiply by 100 to express as a percentage.
AI-generated suggestions, not part of the candidate's original notes. May be inaccurate — verify before relying on them.
Break the problem into three logical layers: filtering, aggregation, and percentage calculation. First, identify posts flagged with probability > 0.5 and ensure they had at least one view in the given month. Then, for each month and violation type, count distinct posts and compute the percentage share of all flagged post/violation pairs in that month.
Pro tip: Clarify the grain of the data and the definition of 'flagged post/violation pairs' upfront—this avoids double-counting and ensures the percentage denominator is correct. Also, consider edge cases like months with no flagged posts.
Select posts with flag probability > 0.5 and join with view data to keep only those with at least one view in the target month.
Group by month and violation type, then count distinct post IDs to get the numerator for each group.
For each month, calculate the total number of distinct flagged post/violation pairs (or total distinct posts if each post has one violation type) to serve as the denominator.
Divide each group's distinct post count by the monthly total and multiply by 100 to get the percentage share.
Check for anomalies, ensure percentages sum to 100% per month, and format the output clearly.
AI-generated suggestions, not part of the candidate's original notes. May be inaccurate — verify before relying on them.