Session Lengths by Country: Long-Session Average, 5-Minute Histogram, Similar Pairs

You have a table of user sessions with one row per session and two columns: | Column | Meaning | |---|---| | `country` | The user's country | | `session_time` | Length of the session, in seconds | Answer the three parts below in SQL or Python (pandas), whichever you prefer. Call the table `sessions` (or the DataFrame `df`). ### Clarifying Questions - Can `session_time` or `country` be missing or invalid (a zero or negative length, one country spelled two ways), and should those rows be left out? - Should a session of exactly 180 seconds count as longer than 3 minutes? ### Part 1 — Average length of long sessions Find the average `session_time` across sessions that last longer than 3 minutes (180 seconds). ```hint Check the edge cases Look at what your filter does with a session of exactly 180 seconds and with rows whose length is missing, and say what unit the result is in. ``` #### What This Part Should Cover - A filter that matches the stated boundary - Correct handling of missing values and of the case where no session qualifies - A clearly labeled result (seconds or minutes, rounding) ### Part 2 — Histogram in 5-minute bins Plot a histogram of session lengths using 5-minute (300-second) bins. First explain how you would prepare the data: build a two-column table, one column for the bin and one for the number of sessions in it, then plot from that table. ```hint Give every session one home Decide what a bin's label means (for example, its starting second) and check which bin a session of exactly 300 seconds lands in. ``` #### What This Part Should Cover - A bin assignment in which every session falls in exactly one bin with clear edges - Whether bins with no sessions appear, and how the long tail is shown - A readable plot with labeled axes ### Part 3 — Countries with similar session volume Call two countries similar when their numbers of sessions are within 10% of each other. Produce every pair of similar countries as a table with the columns `country_a` and `country_b`. ```hint Make the test symmetric Reduce the data to one count per country, then check that your 10% rule gives the same verdict whichever country of a pair is listed first. ``` #### Clarifying Questions for this Part - Is "within 10%" measured against the larger count, the smaller count or the average of the two? - Should each pair appear once or in both orders, and can a country be paired with itself? #### What This Part Should Cover - A precise, symmetric definition of "within 10%" - A pairwise comparison with no self-pairs and no duplicate pairs - A deterministic output order and a note on how the approach scales ### What a Strong Answer Covers - Data-quality checks before computing anything - Explicit choices at every boundary: 180 seconds, the bin edges and the 10% rule - Correct, readable SQL or pandas for each part - Interpretation of the results, such as what skewed session lengths mean for the average and the chart ### Follow-up Questions - If session lengths turn out heavily right-skewed, would you report the Part 1 average, a median or both, and why? - Country A is similar to B and B is similar to C, but A is not similar to C. How would you explain that to a stakeholder who wants groups of similar countries? - How would Part 3 change if similarity also required the countries' average session lengths to be within 10%?