Quick Overview

This question evaluates proficiency in SQL-based data manipulation and aggregation, time-window filtering, percentage calculations, and the application of basic statistical hypothesis testing for comparing user engagement across categories.

Analyze Oculus App Engagement with SQL Queries

Company: Meta

Role: Data Scientist

Category: Data Manipulation (SQL/Python)

Difficulty: medium

Interview Round: Onsite

AppUsage +---------+--------+-----------+--------------+------------+ | user_id | app_id | category | minutes_spent| usage_date | +---------+--------+-----------+--------------+------------+ | 101 | 12 | Social | 45 | 2023-04-15 | | 102 | 17 | Game | 30 | 2023-04-16 | | 101 | 17 | Game | 20 | 2023-04-17 | | 103 | 22 | Fitness | 25 | 2023-04-17 | | 104 | 12 | Social | 60 | 2023-04-18 | +---------+--------+-----------+--------------+------------+ ##### Scenario Analyzing Oculus app engagement over the last 30 days ##### Question Write SQL to find the most used app (total minutes) in the past 30 days. Write SQL to compute the percentage of time spent in each app category in the past 30 days. How would you test the hypothesis that users of the "Social" category are more regularly engaged than "Game" users? ##### Hints Use date filters, aggregates, window functions; propose a statistical test.

Overview: This question evaluates proficiency in SQL-based data manipulation and aggregation, time-window filtering, percentage calculations, and the application of basic statistical hypothesis testing for comparing user engagement across categories.

Most used app by minutes

Using the AppUsage table, find the single most used app by total minutes for usage between 2023-04-01 and 2023-04-30. Return one row with app_id and total_minutes.

Tables

AppUsage(user_id INTEGER, app_id INTEGER, category VARCHAR, minutes_spent INTEGER, usage_date DATE)

Hints

  1. Filter to the required date range before aggregating.
  2. Aggregate minutes_spent by app_id, then use a window function to pick the top app (break ties by app_id).

Category time percentages

Using the AppUsage table, compute the percentage of total minutes spent in each app category for usage between 2023-04-01 and 2023-04-30. Return category, minutes_spent, and pct_of_total_minutes rounded to two decimals.

Tables

AppUsage(user_id INTEGER, app_id INTEGER, category VARCHAR, minutes_spent INTEGER, usage_date DATE)

Hints

  1. First compute total minutes per category within the date window.
  2. Compute the grand total of minutes and divide each category's minutes by this total; multiply by 100 and round to 2 decimals.

Prep test: Social vs Game engagement

Using the AppUsage table, prepare per-user engagement metrics for the Social and Game categories for usage between 2023-04-01 and 2023-04-30, to support testing the hypothesis that Social users are more regularly engaged than Game users. Output user_id, category, active_days, total_minutes, and avg_minutes_per_active_day.

Tables

AppUsage(user_id INTEGER, app_id INTEGER, category VARCHAR, minutes_spent INTEGER, usage_date DATE)

Hints

  1. Compute per-user active days as the count of distinct usage_date values per user and category within the date window.
  2. Also compute total minutes per user and category, then average minutes per active day as total_minutes / active_days.

Loading coding console...