SQL for Product Managers
Master essential SQL queries every PM needs for data-driven decision making. From basics to advanced analytics with code examples.
31. Write a query to find all unique job titles from the employees table.
Table: employees +---------------+----------+ | Column Name | Type | +---------------+----------+ | id | int | | employee_id | int | | employee_name | varchar | | name | varchar | | job_title | varchar | | department_id | int | | department | varchar | | manager_id | int | | salary | decimal | | hire_date | varchar | +---------------+----------+
Write a SQL query in MySQL to solve the problem above. Return the result table in any order.
Example 1:
Input (employees):
| id | employee_id | employee_name | name | job_title | department_id | department | manager_id | salary | hire_date |
|---|---|---|---|---|---|---|---|---|---|
| 1 | 1 | Ananya Iyer | Ananya Iyer | VP of Product | 10 | Product | NULL | 240000 | 2020-02-10 |
| 2 | 2 | Kabir Sharma | Kabir Sharma | Senior Product Manager | 10 | Product | 1 | 175000 | 2021-06-15 |
| 3 | 3 | Neha Gupta | Neha Gupta | Product Manager | 10 | Product | 1 | 140000 | 2022-03-01 |
| 4 | 4 | Vikram Rao | Vikram Rao | Staff Engineer | 20 | Engineering | 1 | 195000 | 2021-09-20 |
| id | employee_id | employee_name | name | job_title | department_id | department | manager_id | salary | hire_date |
|---|---|---|---|---|---|---|---|---|---|
| 1 | 1 | Ananya Iyer | Ananya Iyer | VP of Product | 10 | Product | NULL | 240000 | 2020-02-10 |
| 2 | 2 | Kabir Sharma | Kabir Sharma | Senior Product Manager | 10 | Product | 1 | 175000 | 2021-06-15 |
| 3 | 3 | Neha Gupta | Neha Gupta | Product Manager | 10 | Product | 1 | 140000 | 2022-03-01 |
| 4 | 4 | Vikram Rao | Vikram Rao | Staff Engineer | 20 | Engineering | 1 | 195000 | 2021-09-20 |
| 5 | 5 | Priya Menon | Priya Menon | Data Scientist | 30 | Analytics | 4 | 155000 | 2023-01-11 |
| 6 | 6 | Rohan Das | Rohan Das | Product Manager | 10 | Product | 2 | 132000 | 2023-07-08 |
SQL Cheat Sheet for PM Interviews
The most common SQL concepts asked in product manager interviews, with the question patterns interviewers use.
SELECT & WHERE
Pull metrics for a specific segment, e.g. orders last 30 days.
JOINs
Combine users with events or orders to study behavior across tables.
GROUP BY & HAVING
Compute DAU/MAU, retention and conversion by cohort.
Subqueries & CTEs
Break funnel analysis into readable, staged queries.
Window Functions
Rank users, running totals and period-over-period growth.
Query Performance
Explain indexes and why a slow dashboard query is slow.
SQL Practice Challenges (655)
Pick any challenge below to open its schema and solve in PostgreSQL, MySQL, or Oracle.
| Status | Challenge | Difficulty | Topic | Action |
|---|---|---|---|---|
#31Write a query to find all unique job titles from the employees table. | Easy | Practical Basics | ||
#32Select all details of customers who live in 'New York' or 'London'. | Easy | Practical Basics | ||
#33Find all products that start with the letter 'A'. | Easy | Practical Basics | ||
#34Find all orders where the shipping_date is missing (not yet shipped). | Easy | Practical Basics | ||
#35Count the total number of orders placed in 2023. | Easy | Practical Basics | ||
#36Find the total sales (revenue) for each product category. | Easy | Practical Basics | ||
#37Find categories that have a total revenue greater than $10,000. | Easy | Practical Basics | ||
#38List the top 5 highest-paid employees. | Easy | Practical Basics | ||
#39Calculate the average order value (AOV) from the orders table. | Easy | Practical Basics | ||
#40Create a column called order_status that labels orders as 'High Value' if total > 500, else 'Standard'. | Easy | Practical Basics | ||
#41List all orders along with the customer name who placed them. | Medium | Intermediate Joins | ||
#42List all customers, including those who have never placed an order. | Medium | Intermediate Joins | ||
#43Find customers who have never placed an order. | Medium | Intermediate Joins | ||
#44Find employees who earn more than their managers. | Medium | Intermediate Joins | ||
#45Find products that have a price higher than the average price of all products. | Medium | Intermediate Subqueries | ||
#46Find the average number of orders placed per customer. | Medium | Intermediate Subqueries | ||
#47Find suppliers who supply at least one product priced over $100 using EXISTS. | Medium | Intermediate Subqueries | ||
#48Combine a list of customers from table A and table B, keeping duplicates. | Medium | Intermediate Subqueries | ||
#49Create a full name column by combining first_name and last_name. | Medium | Intermediate Subqueries | ||
#50Find orders placed in the last 7 days. | Medium | Intermediate Subqueries | ||
#51Rank employees by salary within each department. | Hard | Advanced Analytics | ||
#53Find the 3rd highest distinct salary in the company. | Hard | Advanced Analytics | ||
#54Calculate the cumulative sales (running total) day by day. | Hard | Advanced Analytics | ||
#55Calculate a 7-day moving average of signups. | Hard | Advanced Analytics | ||
#56Compare today's sales to yesterday's sales (create a column for 'previous_sales'). | Hard | Advanced Analytics | ||
#57Find the number of days between an order and the next order by the same customer. | Hard | Advanced Analytics | ||
#58Calculate what percentage each product contributes to total revenue. | Hard | Advanced Analytics | ||
#59For every user, find the very first product they purchased. | Hard | Advanced Analytics | ||
#60Group users by their signup month and count how many users signed up in each month (Cohort Analysis). | Hard | Advanced Analytics | ||
#61Employees Earning More Than Their Managers | Medium | Joins | ||
#62Second Highest Salary | Medium | Aggregations | ||
#63Customers Who Never Order | Medium | Joins | ||
#64Duplicate Emails | Medium | Aggregations | ||
#65Classes More Than 5 Students | Medium | Aggregations | ||
#66Consecutive Numbers | Medium | Joins | ||
#67Supercloud Customer | Medium | Aggregations | ||
#68Rising Temperature | Medium | Joins | ||
#69Department Top Three Salaries | Hard | Window Functions | ||
#70Matching Skills | Medium | Aggregations | ||
#71Patients With a Condition | Easy | String & Date Functions | ||
#72Invalid Tweets Detection | Easy | Basics | ||
#73Find Followers Count | Medium | Aggregations | ||
#74Article Views I | Easy | Basics | ||
#75Recyclable and Low Fat Products | Easy | Basics | ||
#76Daily Leads and Partners | Medium | Aggregations | ||
#77Compressed Mode | Medium | Aggregations | ||
#78Retail Industry Leader | Medium | Subqueries & CTEs | ||
#79Find Customer Referee | Easy | Basics | ||
#80Top Trading Cities | Medium | Joins | ||
#81Food Delivery Drivers - Earnings Filter | Easy | Basics | ||
#82Pizza Perfection | Easy | Basics | ||
#83Second Day Confirmations | Medium | Joins | ||
#84Sales Mavericks - The XYZ Company Avoiders | Medium | Joins | ||
#85Popular Courses | Medium | Aggregations | ||
#86Product Sale Details | Medium | Joins | ||
#87Bonus Report | Medium | Joins | ||
#88High Earning Employees Relative to Their Managers | Medium | Joins | ||
#89User Tweet Distribution | Medium | Aggregations | ||
#90Count Followers for Users | Medium | Aggregations | ||
#91Duplicate Phone Numbers | Medium | Aggregations | ||
#92Device Viewership Analysis | Medium | Aggregations | ||
#93High Protein and Non-Perishable Items | Easy | Basics | ||
#94Find Unreferred Customers | Easy | Basics | ||
#95Salaries for Associate Professors | Easy | Basics | ||
#96Missing Phone Numbers | Easy | Basics | ||
#97Best Selling Products | Medium | Aggregations | ||
#98Consistent Communicators | Medium | Aggregations | ||
#99Managers with 5 or More Direct Reports | Medium | Joins | ||
#100User Who are not Customers | Medium | Joins | ||
#101Teams Super Chatters | Medium | Aggregations | ||
#102Pharmacy Sales Analysis II | Medium | Aggregations | ||
#103Large Countries | Easy | Basics | ||
#104Event Frequency Analysis | Medium | Aggregations | ||
#105Self-Viewed Articles | Easy | Basics | ||
#106Customers with Zero Transactions | Medium | Joins | ||
#107Exam Attendance | Medium | Joins | ||
#108Largest Unique Number | Medium | Aggregations | ||
#109Patients with Type I Diabetes | Easy | String & Date Functions | ||
#110Unfinished Assembly Parts | Easy | Basics | ||
#111Highest Transacting Customers | Medium | Aggregations | ||
#112Reassigning Employee Unique Identifiers | Medium | Joins | ||
#113Engaging Movies | Easy | Basics | ||
#114Average Processing Time per Machine | Medium | Joins | ||
#115Unique Subjects by Instructor | Medium | Aggregations | ||
#116Reformat User Names | Easy | String & Date Functions | ||
#117Products Ordered in a Month | Easy | String & Date Functions | ||
#118Data Science Proficiency | Medium | Aggregations | ||
#119Unpopular Facebook Pages | Medium | Joins | ||
#120Detect Duplicate Job Listings | Medium | Aggregations | ||
#121Monthly Average Star Ratings | Medium | Aggregations | ||
#122Amazon Order Insights | Medium | Aggregations | ||
#123Pharmacy Sales Analysis I | Medium | Aggregations | ||
#124Manufacturer Wise Drug Sales | Medium | Aggregations | ||
#125DiCaprio's Oscar-Nominated Performances | Easy | Basics | ||
#126Coffee Craze | Medium | Joins | ||
#127Top Authors | Medium | Aggregations | ||
#128Retrieving Customer Purchases | Medium | Joins | ||
#129Customer and Subscription Information | Medium | Joins | ||
#130Departmental Newcomers | Hard | Window Functions | ||
#131New Marketing Hires in Q3 | Easy | String & Date Functions | ||
#132Customer Engagement Across Clients | Medium | Aggregations | ||
#133Cooling Trends | Medium | Joins | ||
#134Last Month Daily Active Users | Medium | Aggregations | ||
#135Managerial Hierarchy Analysis | Medium | Subqueries & CTEs | ||
#136Product Average Price Analysis | Medium | Aggregations | ||
#137Average Employee Experience | Medium | Joins | ||
#138Contest Attendance Percentage | Medium | Aggregations | ||
#139Query Performance Analysis | Medium | Aggregations | ||
#140Group Products Sold by Date | Easy | String & Date Functions | ||
#141Identify Users with Valid E-Mail | Easy | String & Date Functions | ||
#142Time between First and Last Post | Medium | Aggregations | ||
#143Calculate App CTR | Medium | Aggregations | ||
#144Monthly Credit Card Issuance Disparity | Medium | Aggregations | ||
#145Thrice is Nice - Actor Director Collaborations | Medium | Aggregations | ||
#146Departmental Salary Difference | Medium | Subqueries & CTEs | ||
#147Overdue Salary Adjustments | Easy | String & Date Functions | ||
#148Last Vehicle Deployment | Hard | Window Functions | ||
#149Analysis of Ratings for The Matrix | Medium | Aggregations | ||
#150Department Salary Insights | Medium | Aggregations | ||
#151Average Number of Bathrooms and Bedrooms | Medium | Aggregations | ||
#152Website Visits per Month | Medium | Aggregations | ||
#153Customer Order Details | Medium | Joins | ||
#154Employee Primary Department | Medium | Aggregations | ||
#155Average Monthly Sales: Store vs. Company Comparison | Medium | Subqueries & CTEs | ||
#156Customer Purchase Analysis for Flipkart | Medium | Aggregations | ||
#157Student's Fifth Course Enrollment at NextLeap | Hard | Window Functions | ||
#158Ride-Sharing Driver Availability Calculation | Hard | Window Functions | ||
#159User Ticket Purchases and Count | Hard | Window Functions | ||
#160PhonePe Transaction Rolling Averages | Hard | Window Functions | ||
#161Best-Selling Cosmetics Products | Hard | Window Functions | ||
#162Top 5 Bollywood Singers | Hard | Window Functions | ||
#163Customer Support Analysis | Medium | Subqueries & CTEs | ||
#164Best Value for Money Players | Medium | Joins | ||
#165Find the Top Performers | Medium | Joins | ||
#166Identify Potential Underperformers | Easy | Basics | ||
#167Team Performance Summary | Medium | Aggregations | ||
#168Team Budget Efficiency | Easy | Basics | ||
#169Player Transfer Analysis | Medium | Joins | ||
#170Detect Peak Traffic Days at the Mall | Hard | Window Functions | ||
#171Swap Consecutive Task Assignments | Hard | Window Functions | ||
#172First Course Enrollment Analysis | Medium | Subqueries & CTEs | ||
#173Inactive Player Reactivation | Medium | Subqueries & CTEs | ||
#174Tracking the Longest Rainfall Streak for Each City | Hard | Window Functions | ||
#175Filtering Active Users by 'Stranger Things' Views | Medium | Joins | ||
#176FMCG View-to-Purchase Funnel Analysis | Medium | Aggregations | ||
#177Calculate Monthly Average Show Rating | Easy | String & Date Functions | ||
#178Rank Employees by Performance Score | Hard | Window Functions | ||
#179Employees with Consecutive Salary Increases | Hard | Window Functions | ||
#180Calculate the Average Number of Job Applications per month | Easy | String & Date Functions | ||
#181Assessing Google Ad Click-Through and Conversion Rates | Medium | Aggregations | ||
#182Analyzing Netflix User Behavior and Content Ratings | Medium | Aggregations | ||
#183Calculate the MoM percentage change in revenue for Amazon | Hard | Window Functions | ||
#184Calculate Famous Percentage of Each User Based on Followers | Medium | Aggregations | ||
#185Customers with Exactly Two Product Purchases in a Month | Medium | Aggregations | ||
#186Detecting Significant Sales Drop Days for Flipkart | Hard | Window Functions | ||
#187Analyze Daily Content Downloads by Subscription Type | Medium | Joins | ||
#188Total number of listings | Medium | Aggregations | ||
#189What is SQL and why should a Product Manager know it? | Easy | Basics | ||
#190What is a relational database, and how is it different from a spreadsheet? | Easy | Basics | ||
#191What is a primary key and why does it matter for product data analysis? | Easy | Basics | ||
#192What is a foreign key and how does it connect product data tables? | Easy | Basics | ||
#193What does a SQL schema tell you about product data organization? | Easy | Basics | ||
#194What is the difference between a row and a column in a database table? | Easy | Basics | ||
#195What are the most common SQL data types and why do they matter? | Easy | Basics | ||
#196What is NULL in SQL and how can it distort product metrics? | Easy | Basics | ||
#197What is the difference between a fact table and a dimension table? | Easy | Basics | ||
#198What does database normalization mean for the queries a PM writes? | Easy | Basics | ||
#199What does a basic SELECT statement do and what are its core clauses? | Easy | Basics | ||
#200What are the major SQL dialects a PM might encounter? | Easy | Basics | ||
#201How can a PM use SQL to verify the results of an A/B test? | Easy | Basics | ||
#202What is the difference between a database and a data lake? | Easy | Basics | ||
#203What is the logical order of SQL query execution? | Easy | Basics | ||
#204What is a SQL comment and why is it important in analytics work? | Easy | Basics | ||
#205What does DISTINCT do and when would a PM use it? | Easy | Basics | ||
#206What is a data warehouse and how does it differ from a production database? | Easy | Basics | ||
#207What is ETL and why does it matter for data accuracy? | Easy | Basics | ||
#209What is an aggregate function in SQL? Give three product-relevant examples. | Easy | Basics | ||
#210What is GROUP BY and how does it relate to segmenting product metrics? | Easy | Basics | ||
#211What is the difference between WHERE and HAVING? | Easy | Basics | ||
#212What factors affect SQL query performance and why should a PM care? | Easy | Basics | ||
#214Write a query to retrieve all columns from the users table. | Easy | Practical Basics | ||
#215Write a query to select only the user_id and email columns from the users table. | Easy | Practical Basics | ||
#216Write a query to count the total number of users in the users table. | Easy | Practical Basics | ||
#217Write a query to retrieve all users ordered by their signup date, newest first. | Easy | Practical Basics | ||
#218Write a query to retrieve the top 10 orders by revenue amount. | Easy | Practical Basics | ||
#219Write a query to get a list of unique countries from the users table. | Easy | Practical Basics | ||
#220Write a query to count users who signed up in the last 7 days. | Easy | Practical Basics | ||
#221Write a query to retrieve all events triggered by a specific user. | Easy | Practical Basics | ||
#222Write a query to get all orders created on January 15, 2024. | Easy | Practical Basics | ||
#223Write a query to calculate the total revenue from all completed orders. | Easy | Practical Basics | ||
#224Write a query to find the average order value across all completed orders. | Easy | Practical Basics | ||
#225Write a query to find the maximum and minimum order amounts. | Easy | Practical Basics | ||
#226Write a query to count support tickets grouped by their current status. | Easy | Practical Basics | ||
#227Write a query to count new user signups by calendar month. | Easy | Practical Basics | ||
#228Write a query to find a user by their email address. | Easy | Practical Basics | ||
#229Write a query to count the number of distinct subscription plan types currently active. | Easy | Practical Basics | ||
#230Write a query to count active subscribers grouped by plan type. | Easy | Practical Basics | ||
#231Write a query to list all distinct event names tracked in the events table. | Easy | Practical Basics | ||
#232Write a query to calculate total session duration per user over the last 30 days. | Easy | Practical Basics | ||
#233Write a query to count orders by status and show what percentage of total each status represents. | Easy | Practical Basics | ||
#234Write a query to find all users who have the feature flag 'dark_mode' enabled. | Easy | Practical Basics | ||
#235Write a query to find users who have never made a purchase. | Easy | Practical Basics | ||
#236Write a query to calculate the average number of orders per user. | Easy | Practical Basics | ||
#237Write a query to show daily new user registrations for the last 14 days. | Easy | Practical Basics | ||
#238Write a query to retrieve all orders where the amount exceeds $500. | Easy | Practical Basics | ||
#239Write a query to find users who have not logged in since January 1, 2024. | Easy | Practical Basics | ||
#240Write a query to compute total monthly revenue for the year 2023. | Easy | Practical Basics | ||
#241Write a query to find the top 5 countries with the most registered users. | Easy | Practical Basics | ||
#242Write a query to count users per country using descriptive column aliases. | Easy | Practical Basics | ||
#243Write a query to find how many users were active in both week 1 and week 2 of January 2024. | Easy | Practical Basics | ||
#244Write a query to find the date of each user's first completed order. | Easy | Practical Basics | ||
#245Write a query to count users per app platform and show the average session duration per platform for users who signed up in Q1 2024. | Easy | Practical Basics | ||
#246Write a query to calculate the daily conversion rate from sessions to purchases on June 1, 2024. | Easy | Practical Basics | ||
#247Write a query to count active products grouped by category. | Easy | Practical Basics | ||
#248Write a query to calculate daily active users for the past 30 days. | Easy | Practical Basics | ||
#249Write a query to show the 20 most recently updated rows in the user_profiles table. | Easy | Practical Basics | ||
#250Write a query to find all orders where the amount is NULL. | Easy | Practical Basics | ||
#251Write a query to find the oldest and newest user registration dates in the database. | Easy | Practical Basics | ||
#252Write a query to count users grouped by the device type they first registered on. | Easy | Practical Basics | ||
#253Write a query to get total completed order revenue for the current calendar year. | Easy | Practical Basics | ||
#254Write a query to count the number of events per hour of the day using yesterday's data. | Easy | Practical Basics | ||
#255Write a query to retrieve all users from the United States or Canada. | Easy | Practical Basics | ||
#256Write a query to produce a single-row metrics summary showing total users, total orders, total revenue, and average order value. | Easy | Practical Basics | ||
#257Write a query to show each user's signup date, their first purchase date, and the number of days between them. | Easy | Practical Basics | ||
#258Write a query to retrieve records 51 through 100 from the orders table ordered by creation date. | Easy | Practical Basics | ||
#259Write a query to categorize each completed order as Small, Medium, or Large based on its amount | Easy | Practical Basics | ||
#260Write a query to find users who have made exactly one completed purchase. | Easy | Practical Basics | ||
#261Write a query to compute total revenue by product category. | Easy | Practical Basics | ||
#262Write a query to compare average monthly sessions for free vs. paid users over the last 30 days | Easy | Practical Basics | ||
#263Write a query to compare total new signups between 2023 and 2024. | Easy | Practical Basics | ||
#264Write a query to count events per event type for yesterday to monitor tracking completeness. | Easy | Practical Basics | ||
#265Write a query to count distinct active users by day of the week over the last 4 weeks. | Easy | Practical Basics | ||
#266Write a query to count users whose subscription was cancelled during the previous calendar month. | Easy | Practical Basics | ||
#267Write a query to identify orders that were followed by a refund event and rank them by refund amount while categorizing lifetime spend. | Easy | Practical Basics | ||
#268Write a single query that returns DAU, WAU, and MAU together for use in a product dashboard. | Easy | Practical Basics | ||
#269Write a query to retrieve all orders where the amount is greater than all users who signed up in the last 30 days and whose plan type is premium, using the IN operator. | Easy | Filtering & Conditions | ||
#270Write a query to find all support tickets filtered by both status using the CASE operator and grouped by agent to calculate average resolution time from your analytics data. | Easy | Filtering & Conditions | ||
#271Write a query to find all orders with amounts between $100 and $500. | Easy | Filtering & Conditions | ||
#272Write a query to find all products whose names or descriptions starts with the word 'Pro'. | Easy | Filtering & Conditions | ||
#273Write a query to add a human-readable label to each user based on their subscription status. | Easy | Filtering & Conditions | ||
#274Write a query to display a user's country and replace any NULL country values with the label 'Unknown'. | Easy | Filtering & Conditions | ||
#275Write a query to find all users who signed up between March 1 and March 31, 2024. | Easy | Filtering & Conditions | ||
#276Write a query to find all orders where the status is neither 'completed' nor 'pending'. | Easy | Filtering & Conditions | ||
#277Write a query to find users who have a premium plan and are located in the United States. | Easy | Filtering & Conditions | ||
#278Write a query to find users who are either on a premium plan or located in Germany. | Easy | Filtering & Conditions | ||
#279Write a query to find all events of type 'purchase' or 'add_to_cart' from the last 7 days. | Easy | Filtering & Conditions | ||
#280Write a query to classify users into engagement tiers based on their number of sessions in the last 30 days. | Easy | Filtering & Conditions | ||
#281Write a query to count how many sessions were started by the top 5% of users with the most sessions. | Easy | Filtering & Conditions | ||
#282Write a query to find all users who registered more than a year ago but have still never made a purchase. | Easy | Filtering & Conditions | ||
#283Write a query to find all users whose email contains the word 'admin'. | Easy | Filtering & Conditions | ||
#284Write a query to find orders where the discount applied is greater than zero. | Easy | Filtering & Conditions | ||
#285Write a query to find users who have both a verified email and an active subscription. | Easy | Filtering & Conditions | ||
#286Write a query to retrieve all events that occurred on a weekend in the last 30 days. | Easy | Filtering & Conditions | ||
#287Write a query to find users whose account was created but who have never started a session. | Easy | Filtering & Conditions | ||
#288Write a query to find all purchases made by users on a trial plan. | Easy | Filtering & Conditions | ||
#289Write a query to find all users who have both logged in and made a purchase within the last 30 days. | Easy | Filtering & Conditions | ||
#290Write a query to find all users who have a NULL value in the phone_number field. | Easy | Filtering & Conditions | ||
#291Write a query to find sessions where the platform is not mobile, meaning it is either desktop or tablet. | Easy | Filtering & Conditions | ||
#292Write a query to label each user as 'New', 'Established', or 'Veteran' based on how long ago they signed up. | Easy | Filtering & Conditions | ||
#293Write a query to find all events where the event properties contain a specific value using a LIKE filter on a JSON-like text field. | Easy | Filtering & Conditions | ||
#294Write a query to find all users who have a subscription expiring in the next 7 days. | Easy | Filtering & Conditions | ||
#295Write a query to find users who completed onboarding but have not used the core feature within 7 days of signing up. | Easy | Filtering & Conditions | ||
#296Write a query to find the number of users who changed their plan type in the last 30 days. | Easy | Filtering & Conditions | ||
#297Write a query to find users who have placed more than 3 orders in a single day. | Easy | Filtering & Conditions | ||
#298Write a query to find all users who have used the product on more than one platform in the last 30 days. | Easy | Filtering & Conditions | ||
#299Write a query to return a column that shows revenue after applying a 10% discount, only for orders above $200. | Easy | Filtering & Conditions | ||
#300Write a query to find all users who have not received any email notification in the last 14 days. | Easy | Filtering & Conditions | ||
#301Write a query to find all sessions where users encountered an error event within the same session. | Easy | Filtering & Conditions | ||
#302Write a query to find all users whose last session was on a mobile device but who originally signed up on desktop. | Easy | Filtering & Conditions | ||
#303Write a query to find the top 10 users by total revenue generated in the last 90 days, but only from users who are on an active subscription. | Easy | Filtering & Conditions | ||
#304Write a query to find users who signed up via a referral but have never made a purchase. | Easy | Filtering & Conditions | ||
#305Write a query to find users who made their first purchase after the trial ended, within 7 days of registering for a trial, and used a coupon code during checkout in the last 30 days. | Easy | Filtering & Conditions | ||
#306Write a query to find users who have more than 5 sessions in the last 30 days. | Easy | Filtering & Conditions | ||
#307Write a query to find all users who registered via the mobile app, completed onboarding, but have not made a purchase in the last 30 days. | Easy | Filtering & Conditions | ||
#308Write a query to find all users who meet the criteria for a win-back campaign: they were active 60 to 90 days ago but have had no sessions in the last 30 days. | Easy | Filtering & Conditions | ||
#309Write a query using INNER JOIN to retrieve all orders along with the email address of the user who placed them. | Medium | Joins | ||
#310Write a query using LEFT JOIN to find all users and their total number of orders, including users who have never placed an order. | Medium | Joins | ||
#311Write a query to find all users who have never placed an order using a LEFT JOIN approach. | Medium | Joins | ||
#312Write a query to retrieve each user's subscription plan details by joining the users table to the plans table. | Medium | Joins | ||
#313Write a query to join the subscriptions table to the users table and show each user's plan type and monthly price. | Medium | Joins | ||
#314Write a query to list all subscription plans and the number of users on each plan, including plan types with no users currently assigned. | Medium | Joins | ||
#315Write a query to find the top 10 users by lifetime revenue using a JOIN between orders and users. | Medium | Joins | ||
#316Write a query to join the sessions table to the users table and show average session duration by country. | Medium | Joins | ||
#317Write a query to show each order along with the product name and category by joining orders, order_items, and products. | Medium | Joins | ||
#318Write a query to find users who have placed an order but have never started a session, which may indicate a purchase via a direct API or bot. | Medium | Joins | ||
#319Write a query to find the average number of support tickets per user alongside their subscription plan, by joining support_tickets, users, and plans. | Medium | Joins | ||
#320Write a query to retrieve all users who have completed the onboarding checklist, along with their first order date, by joining users and orders. | Medium | Joins | ||
#321Write a query to find users who signed up in the same month they made their first purchase, then show their plan type and whether they completed onboarding. | Medium | Joins | ||
#322Write a query to calculate the total revenue generated per user by joining orders to users, for the year 2024. | Medium | Joins | ||
#323Write a query to find the average number of support tickets per user by joining users to support_tickets and grouping the results by users who have been paying for each event type in the last 30 days. | Medium | Joins | ||
#324Write a query to find pairs of users who referred each other using a referrals table joined to the users table. | Medium | Joins | ||
#325Write a query to get a list of users with their most recent session information by joining users and sessions. | Medium | Joins | ||
#326Write a query to count how many users from each acquisition source have placed at least one order. | Medium | Joins | ||
#327Write a query to join events to sessions and calculate the average number of events per session by platform. | Medium | Joins | ||
#328Write a query to find all users who have made a purchase in the last 30 days and have also opened a support ticket in the same period. | Medium | Joins | ||
#329Write a query to find the total revenue generated by each subscription plan in the last 12 months, joining orders to subscriptions to plans. | Medium | Joins | ||
#330Write a query to find users who are on a paid plan but have not logged in during the last 60 days, representing high-value churn risk. | Medium | Joins | ||
#331Write a query using a self-join to find all users who were referred by another user who has since churned. | Medium | Joins | ||
#332Write a query to show the average order value by country. | Medium | Joins | ||
#333Write a query to find the average time between a user's first session and their first purchase by joining users, sessions, and orders. | Medium | Joins | ||
#334Write a query to calculate each user's session count, order count, and total revenue in a single table. | Medium | Joins | ||
#335Write a query to find the top 10 events that drive the most first-time purchases, by joining events to users. | Medium | Joins | ||
#336Write a query to find the most popular products by the number of distinct users who purchased them, joining order_items to products. | Medium | Joins | ||
#337Write a query to find the total revenue generated by users who attended a product webinar, by joining webinar_attendees to orders. | Medium | Joins | ||
#338Write a query to find all sessions where the user had an active premium subscription at the time of the session. | Medium | Joins | ||
#339Write a query to calculate the churn rate by plan type using a JOIN between subscriptions cancelled in the last month and the total active base at the start of that month. | Medium | Joins | ||
#340Write a query to calculate each category's contribution to total revenue as a percentage of total revenue. | Medium | Joins | ||
#341Write a query to find users who have used a promo code and compare their average order value to users who have not. | Medium | Joins | ||
#342Write a query to find users who saw a session with an 'upgrade' event but never completed an upgrade. | Medium | Joins | ||
#343Write a query to identify users whose first purchase was a high-value order above $200, and show how their subsequent purchase behavior differs from other users. | Medium | Joins | ||
#344Write a query to count the number of users who have both a completed profile and an active subscription, by joining user_profiles and subscriptions. | Medium | Joins | ||
#345Write a query to find the average session duration for users on each plan type, joining sessions and users. | Medium | Joins | ||
#346Write a query to find which referral source drives the highest average session count per user in the first 30 days after signup. | Medium | Joins | ||
#347Write a query to calculate net revenue by month, defined as completed order revenue minus refunded order revenue, using a JOIN approach. | Medium | Joins | ||
#348Write a query to find all users who completed at least 3 different feature actions in the last 7 days, by joining events to a feature_actions reference table. | Medium | Joins | ||
#349Write a query to find the first and last order date for each user and calculate their customer lifespan in days. | Medium | Joins | ||
#350Write a query to identify users who complete onboarding and then make a purchase within 7 days, using a JOIN-based approach. | Medium | Joins | ||
#351Write a query to show each user's plan upgrade history by joining subscription_changes to users. | Medium | Joins | ||
#352Write a query to count daily active users broken down by both platform and plan type using a three-table JOIN. | Medium | Joins | ||
#353Write a query to find users who received a coupon via an email campaign and then made a purchase on the same day they received a marketing email, by joining email_sends to sessions. | Medium | Joins | ||
#354Write a query to find the top 5 most common user journey sequences by joining consecutive events within the same session. | Medium | Joins | ||
#355Write a query to find users who triggered a feature event after the launch date, comparing the 30 days before and after the launch, using events joined to orders. | Medium | Joins | ||
#356Write a query to find users who are in both a specific A/B test variant and have placed an order in the last 14 days, by joining ab_test_assignments to orders. | Medium | Joins | ||
#357Write a query to find all users with an active profile who have made a purchase but have not started a subscription, by joining page_views to users and subscriptions. | Medium | Joins | ||
#358Write a query to calculate the ratio of support tickets to active users by month, joining support_tickets to sessions. | Medium | Joins | ||
#359Write a query to find all users who have both enabled notifications and have a session in the last 7 days, by joining notification_settings to sessions. | Medium | Joins | ||
#360Write a query to find all users and their first session date, joined across users and sessions. | Medium | Joins | ||
#361Write a query to identify users who completed a purchase and then cancelled their subscription within 30 days, suggesting buyer's remorse or unmet expectations. | Medium | Joins | ||
#362Write a query to find users who were in an A/B test and show their session count and revenue in the 14 days following assignment. | Medium | Joins | ||
#363Write a query to find which content categories are most viewed by users who later subscribe, by joining content_views to subscriptions. | Medium | Joins | ||
#364Write a query to build a cohort retention table showing what percentage of each monthly signup cohort was still active in months 1, 2, and 3 after signup. | Medium | Joins | ||
#365Write a query to find all users whose subscription revenue exceeds the median user revenue, by joining subscriptions to orders. | Medium | Joins | ||
#366Write a query to count the number of sessions per user in the week before and after a major product update, using a self-join approach on sessions. | Medium | Joins | ||
#367Write a query to find which features are most used by users who later renew their subscription, by joining feature_usage to subscription_renewals. | Medium | Joins | ||
#368Write a query to identify the users who refer the most paying users and calculate their total referred revenue, to measure the quality of the referral program. | Medium | Joins | ||
#369Write a query to find the average number of sessions per user, normalized by account age, for each subscription tier. | Medium | Joins | ||
#370Write a query to find users who completed a three-step checkout flow in the correct sequence, by joining events in the correct sequence. | Medium | Joins | ||
#371Write a query to find users who are subscribed to a plan that has been deprecated, by joining users and subscriptions to a plan status table. | Medium | Joins | ||
#372Write a query to calculate week-over-week revenue growth by joining weekly revenue aggregates to themselves using a self-join. | Medium | Joins | ||
#373Write a query to find users who have a high session count but zero purchases, representing highly engaged non-converters. | Medium | Joins | ||
#374Write a query to identify the most common reason for subscription cancellation by joining cancellations to a cancellation_reasons table. | Medium | Joins | ||
#375Write a query to measure the impact of the onboarding checklist on user retention by comparing 30-day retention for users who completed it vs. those who did not. | Medium | Joins | ||
#376Write a query to find all users who have referred someone and have an above-average number of sessions, to identify your most engaged referrers. | Medium | Joins | ||
#377Write a query to calculate the 90-day revenue per cohort, joining users and orders and grouping by signup month. | Medium | Joins | ||
#378Write a query to find the most valuable user segments by combining plan type, acquisition source, and country into a composite segment and ranking by average revenue per user. | Medium | Joins | ||
#379Write a query using a subquery in the WHERE clause to find all users who have placed at least one order. | Medium | Subqueries & CTEs | ||
#380Write a query to find all users who have not placed any orders, using a NOT IN subquery. | Medium | Subqueries & CTEs | ||
#381Write a query using a subquery in the FROM clause to calculate the average number of sessions per user and then find users above that average. | Medium | Subqueries & CTEs | ||
#382Write a query to find the user who generated the highest total revenue using a subquery. | Medium | Subqueries & CTEs | ||
#383Write a query using a correlated subquery to find users whose total order value is above the average order value for their own country. | Medium | Subqueries & CTEs | ||
#384Write a query using a subquery to find the top 10 most active users by session count in the last 30 days, then retrieve their full profile information. | Medium | Subqueries & CTEs | ||
#385Write a query to find all products that have been ordered more times than the average number of orders per product. | Medium | Subqueries & CTEs | ||
#386Write a query to find all users who signed up in the month with the highest total signups, using a subquery to identify that month. | Medium | Subqueries & CTEs | ||
#387Write a query to calculate each user's percentage contribution to total revenue using a subquery. | Medium | Subqueries & CTEs | ||
#388Write a query to find all sessions that lasted longer than the 90th percentile session duration. | Medium | Subqueries & CTEs | ||
#389Write a query to find all users who received a coupon that was never redeemed, using a subquery to find unused coupon codes. | Medium | Subqueries & CTEs | ||
#390Write a query to find users whose most recent order was in January 2025, using a subquery to get the user's max order date. | Medium | Subqueries & CTEs | ||
#391Write a query to find the month with the highest total revenue using a subquery, then pull all orders from that month. | Medium | Subqueries & CTEs | ||
#392Write a query using a subquery in the SELECT clause to show each user alongside the total number of users in their country. | Medium | Subqueries & CTEs | ||
#393Write a query to find all users who signed up after the most recent product launch, using a subquery to retrieve the launch date. | Medium | Subqueries & CTEs | ||
#394Write a query to find users who have made more orders than the average user in their acquisition cohort month. | Medium | Subqueries & CTEs | ||
#395Write a query using a subquery to find the top 10 countries by revenue, then filter the users table to only include those countries. | Medium | Subqueries & CTEs | ||
#396Write a query using a subquery to calculate, for each event type, what percentage of the total number of distinct events tracked that day it represents. | Medium | Subqueries & CTEs | ||
#397Write a query to find all users who placed their first order within 7 days of signing up, using a subquery to calculate the first order date. | Medium | Subqueries & CTEs | ||
#398Write a query to find users whose session count is above the average session count of all users who have at least one purchase, using a subquery. | Medium | Subqueries & CTEs | ||
#399Write a subquery to find users who have had more than 3 support tickets and check if they are still on an active subscription. | Medium | Subqueries & CTEs | ||
#400Write a query using a subquery to find events that occurred after each user's most recent subscription start date. | Medium | Subqueries & CTEs | ||
#401Write a query to find users who have had below the median number of sessions since signing up, using a CTE to separate session counts across all users. | Medium | Subqueries & CTEs | ||
#402Write a query using a subquery to find the three most common event types triggered in the 24 hours after a user signs up. | Medium | Subqueries & CTEs | ||
#403Write a query to find all orders where the amount is above the average order value for that specific product category, using a correlated subquery. | Medium | Subqueries & CTEs | ||
#404Write a query to calculate a rolling 7-day average of daily signups for each day in the last 60 days. | Medium | Subqueries & CTEs | ||
#405Write a query to find users who have both the highest session count and the highest order count in the same month. | Medium | Subqueries & CTEs | ||
#406Write a query to find users who have used the same promo code twice, using a subquery-based approach. | Medium | Subqueries & CTEs | ||
#407Write a query using a subquery to identify users who triggered the checkout event but did not trigger the purchase_complete event in the same session. | Medium | Subqueries & CTEs | ||
#408Write a query to find users whose session count in the last 30 days is below 25% of the maximum session count among all users in the same period. | Medium | Subqueries & CTEs | ||
#409Write a query to identify product categories where total revenue exceeds the average category revenue. | Medium | Subqueries & CTEs | ||
#410Write a query to find all users who registered in a month where total signups were above the annual average monthly signups. | Medium | Subqueries & CTEs | ||
#411Write a query to identify users who have been inactive in the last 30 days but were previously active, using subqueries for both periods. | Medium | Subqueries & CTEs | ||
#412Write a query to find the average revenue generated per user within their first 90 days, segmented by the quarter they signed up. | Medium | Subqueries & CTEs | ||
#413Write a query using a subquery to find the top 5 events that always precede a subscription cancellation within the last 7 days before cancellation. | Medium | Subqueries & CTEs | ||
#414Write a query to find users whose spending is more than 2x the average spending across all users, using a subquery to calculate the average. | Medium | Subqueries & CTEs | ||
#415Write a query using a subquery to find which day of the week has the most orders, then retrieve all orders from the last occurrence of that day. | Medium | Subqueries & CTEs | ||
#416Write a query to find users who have tried every premium feature using subqueries to check each feature. | Medium | Subqueries & CTEs | ||
#417Write a query using a nested subquery to calculate the month-over-month growth rate of new subscriptions. | Medium | Subqueries & CTEs | ||
#418Write a query to find users who placed an order in January 2024 but not in February 2024, using subqueries to isolate each month's purchasers. | Medium | Subqueries & CTEs | ||
#419Write a query using a subquery to find users whose most recent event was a purchase_complete, indicating the last action they took was a successful transaction. | Medium | Subqueries & CTEs | ||
#420Write a query to find the country with the fastest average time from signup to first purchase, using nested subqueries. | Medium | Subqueries & CTEs | ||
#421Write a query to find all sessions where the user triggered more events than the average number of events for their platform. | Medium | Subqueries & CTEs | ||
#422Write a query using a subquery to find users who have been continuously active for at least 90 days, meaning they have had at least one session in each of the last 4 weeks. | Medium | Subqueries & CTEs | ||
#423Write a query to identify users who have spent above the 75th percentile of revenue in the last 90 days but have not been contacted by customer success in the last 30 days. | Medium | Subqueries & CTEs | ||
#424Write a query to find users who have visited every feature within the product, using a subquery to calculate adoption rates. | Medium | Subqueries & CTEs | ||
#425Write a query using a subquery to find the cohort with the highest 30-day retention rate among users who signed up in the first half of 2024. | Medium | Subqueries & CTEs | ||
#426Write a query to find users who downgraded their plan and subsequently reduced their session frequency, using subqueries to measure both conditions. | Medium | Subqueries & CTEs | ||
#427Write a query using multiple nested subqueries to find the single event that most reliably predicts a user will make a purchase within 7 days. | Medium | Subqueries & CTEs | ||
#428Write a query to find the users with the highest lifetime lifetime value and return only those in the top 5%. | Medium | Subqueries & CTEs | ||
#429Write a query using ROW_NUMBER to assign a unique sequential rank to each user based on their signup date, oldest first. | Hard | Window Functions | ||
#430Write a query using RANK to rank users by their total lifetime revenue, allowing ties to share the same rank. | Hard | Window Functions | ||
#431Write a query using DENSE_RANK to rank products by total units sold without any gaps in the rank sequence. | Hard | Window Functions | ||
#432Write a query using PARTITION BY to rank users by revenue within each country separately. | Hard | Window Functions | ||
#433Write a query using SUM as a window function to calculate a running total of daily revenue. | Hard | Window Functions | ||
#434Write a query using AVG as a window function to calculate a 7-day rolling average of daily active users. | Hard | Window Functions | ||
#435Write a query using LAG to compare each day's revenue to the previous day's revenue and calculate the day-over-day change. | Hard | Window Functions | ||
#436Write a query using LEAD to show each user's next session date after every session they have had. | Hard | Window Functions | ||
#437Write a query using LAG to show each user's first event alongside every subsequent event they triggered. | Hard | Window Functions | ||
#438Write a query using LAG to calculate the time in days between a user's sessions and identify their longest inactivity period. | Hard | Window Functions | ||
#439Write a query using NTILE to divide users into four equal quartiles based on their total revenue. | Hard | Window Functions | ||
#440Write a query using NTILE to identify the top 10% of users by revenue within its category. | Hard | Window Functions | ||
#441Write a query using CUME_DIST to calculate the cumulative distribution of session durations. | Hard | Window Functions | ||
#442Write a query using ROW_NUMBER with PARTITION BY to get the most recent order for each user. | Hard | Window Functions | ||
#443Write a query using AVG as a window function to calculate each day's revenue as a share of the total monthly revenue for each day. | Hard | Window Functions | ||
#444Write a query to calculate the week-over-week percentage change in the number of new signups using LAG. | Hard | Window Functions | ||
#445Write a query using RANK to find the top 3 events by frequency for each platform. | Hard | Window Functions | ||
#446Write a query using SUM as a window function to calculate the running total of new users added each month. | Hard | Window Functions | ||
#447Write a query using LAG to calculate the month-over-month change in monthly active users. | Hard | Window Functions | ||
#448Write a query using ROW_NUMBER to identify the first time each user made a purchase, labeling it as their first purchase and all subsequent ones as repeat purchases. | Hard | Window Functions | ||
#449Write a query to calculate the rolling average order value using AVG as a window function to track the rolling average order value over time. | Hard | Window Functions | ||
#450Write a query using PARTITION BY and ROW_NUMBER to deduplicate a table with duplicate user records, keeping only the most recently created record per email. | Hard | Window Functions | ||
#451Write a query to assign a session number to each user by using ROW_NUMBER, ranking each session by session date. | Hard | Window Functions | ||
#452Write a query to rank each user's most recent order by value within their signup month cohort. | Hard | Window Functions | ||
#453Write a query using LEAD and LAG together to show each day's signups alongside the previous day's and next day's signups for a three-day context view. | Hard | Window Functions | ||
#454Write a query to calculate the 28-day retention rate defined as the percentage of users who had a session in the current week who also had a session in the same week 28 days ago. | Hard | Window Functions | ||
#455Write a query to find each user's longest streak of consecutive days with at least one session. | Hard | Window Functions | ||
#456Write a query using LAG to flag sessions that were preceded by a gap of at least 14 days, helping to identify reactivation events and users who were inactive for more than 14 days before returning. | Hard | Window Functions | ||
#457Write a query using SUM OVER to calculate each country's cumulative share of total users, ordered by user count from largest to smallest. | Hard | Window Functions | ||
#458Write a query to compute the ratio of each user's session count to the average session count of all users in the same plan tier. | Hard | Window Functions | ||
#459Write a query to calculate the number of days between each user's consecutive orders using LAG. | Hard | Window Functions | ||
#460Write a query to show each product's revenue alongside the revenue of the top-selling product in the same category, using a window function. | Hard | Window Functions | ||
#461Write a query to flag users whose days since their last session are more than 2 standard deviations below the average last-session recency for their cohort. | Hard | Window Functions | ||
#462Write a query using window functions to calculate what percentage of total revenue each monthly cohort has contributed cumulatively over time. | Hard | Window Functions | ||
#463Write a query using window functions to identify the top 3 users by revenue within each country, then show only those users. | Hard | Window Functions | ||
#464Write a query to calculate the median session duration by platform using PERCENTILE_CONT as a window function. | Hard | Window Functions | ||
#465Write a query using ROW_NUMBER to flag the first session of each user as their activation session. | Hard | Window Functions | ||
#466Write a query to show each day's signups as a percentage of the peak signup day in the last 90 days. | Hard | Window Functions | ||
#467Write a query to use NTILE to divide each month's orders into 10 deciles by amount and show the revenue threshold at each decile boundary. | Hard | Window Functions | ||
#468Write a query using AVG as a window function to compute a 3-month moving average of monthly revenue. | Hard | Window Functions | ||
#469Write a query using window functions to show each user's order amount alongside their own previous order amount, for a purchase growth analysis. | Hard | Window Functions | ||
#470Write a query to compute the average number of days between orders for repeat buyers, showing how this interval has changed month by month. | Hard | Window Functions | ||
#471Write a query using LAG to calculate a 14-day forward-looking rolling average of daily revenue. | Hard | Window Functions | ||
#472Write a query to calculate the percentage contribution of each user's sessions to their plan tier's total sessions. | Hard | Window Functions | ||
#473Write a query using LAG to flag users whose most recent order amount exceeded their highest-ever single-order value in the last 30 days. | Hard | Window Functions | ||
#474Write a query to compute the month-over-month growth in the number of users who made at least one purchase, using LAG. | Hard | Window Functions | ||
#475Write a query to rank users into deciles based on their total spending and calculate the average revenue per user for each plan type. | Hard | Window Functions | ||
#476Write a query to calculate the year-over-year revenue growth rate for each month. | Hard | Window Functions | ||
#477Write a query using window functions to find users whose session count has been consistently increasing every week for the past 4 weeks. | Hard | Window Functions | ||
#478Write a query using multiple window functions together to produce a comprehensive user engagement leaderboard showing rank, percentile, and comparison to plan average in a single output. | Hard | Window Functions | ||
#479Write a query to calculate daily active users using COUNT(DISTINCT user_id) on the sessions table, and distinguish this from total sessions to avoid double-counting. | Hard | Product Analytics | ||
#480Write a query to calculate the ratio of monthly active users to total registered users. | Hard | Product Analytics | ||
#481Write a query to calculate a user's average session duration by platform and period. | Hard | Product Analytics | ||
#482Write a query to calculate the DAU-to-MAU ratio (stickiness) for each month in the last 6 months. | Hard | Product Analytics | ||
#483Write a query to identify users who complete their first order within 7 days of signup. | Hard | Product Analytics | ||
#484Write a query to build a report on the average order value trend by month and identify the month with the highest growth. | Hard | Product Analytics | ||
#485Write a query to build a classic cohort retention table showing retention at months 1, 2, 3, and 6 for each quarterly signup cohort. | Hard | Product Analytics | ||
#486Write a query to calculate the new user activation rate, defined as the percentage of signups who trigger at least one core feature event within 24 hours. | Hard | Product Analytics | ||
#487Write a query to measure the aha moment by finding which single event is most correlated with users returning for a second session within 7 days. | Hard | Product Analytics | ||
#488Write a query to calculate the churn rate for each month, defined as the percentage of active subscribers at the start of the month who cancel by the end of the month. | Hard | Product Analytics | ||
#489Write a query to calculate the product's L7 engagement metric, defined as the number of days in the last 7 days a user had at least one session. | Hard | Product Analytics | ||
#490Write a query to calculate user-level L28 scores and segment users into engagement tiers. | Hard | Product Analytics | ||
#491Write a query to identify users who are at risk of churning, defined as those who were active in the 30 days prior to the last 14 days but have not been active in the last 14 days. | Hard | Product Analytics | ||
#492Write a query to calculate the Net Revenue Retention (NRR) for the last month, measuring how much of last month's revenue from existing customers was retained this month including expansions. | Hard | Product Analytics | ||
#493Write a query to compute feature adoption rate for each feature, defined as the percentage of active users in the last 30 days who have used each feature at least once. | Hard | Product Analytics | ||
#494Write a query to calculate the average time to activation, defined as the average number of hours between a user's signup and the first time they trigger the core activation event. | Hard | Product Analytics | ||
#495Write a query to measure user engagement depth by counting the number of distinct features each user has touched in the last 30 days. | Hard | Product Analytics | ||
#496Write a query to calculate the 90-day LTV for each signup cohort, defined as the total revenue per user in the 90 days following signup. | Hard | Product Analytics | ||
#497Write a query to compute the monthly revenue churn rate, defined as the revenue lost from cancelled subscriptions as a percentage of the total revenue at the start of the month. | Hard | Product Analytics | ||
#498Write a query to calculate the viral coefficient of your referral program for each month, defined as the average number of new users each existing user brings in. | Hard | Product Analytics | ||
#499Write a query to measure the impact of push notifications on return sessions by comparing session rates on days users received a notification versus days they did not. | Hard | Product Analytics | ||
#500Write a query to calculate the average number of events per session by feature area, to understand which product areas drive the most in-session activity. | Hard | Product Analytics | ||
#501Write a query to build a user journey funnel from signup to first session to first feature use to first purchase. | Hard | Product Analytics | ||
#502Write a query to calculate the engagement score for each user, defined as a weighted combination of sessions, events, and features used in the last 30 days. | Hard | Product Analytics | ||
#503Write a query to find the top 10 drop-off points in the onboarding flow by identifying where users stop triggering onboarding events. | Hard | Product Analytics | ||
#504Write a query to calculate the product's overall health score by combining DAU, 7-day retention, and feature adoption into a single composite index. | Hard | Product Analytics | ||
#505Write a query to measure the impact of a specific in-app tooltip on feature discovery by comparing feature adoption rates among users who saw the tooltip versus those who did not. | Hard | Product Analytics | ||
#506Write a query to identify power users defined as the top 10% by session count who have not yet upgraded to a paid plan. | Hard | Product Analytics | ||
#507Write a query to track the weekly new user acquisition by channel for the last 13 weeks. | Hard | Product Analytics | ||
#508Write a query to compute the average session duration trend week over week for the last 12 weeks. | Hard | Product Analytics | ||
#509Write a query to calculate the day-1, day-7, and day-30 retention rates for users acquired through each channel. | Hard | Product Analytics | ||
#510Write a query to calculate the monthly revenue per feature area by joining orders to feature usage to understand which product areas drive the most monetization. | Hard | Product Analytics | ||
#511Write a query to identify users who are likely to convert from free to paid in the next 7 days based on increasing engagement in the last two weeks. | Hard | Product Analytics | ||
#512Write a query to measure how feature usage changes between the first and second month of a user's subscription. | Hard | Product Analytics | ||
#513Write a query to compute the average number of sessions before a user makes their first purchase. | Hard | Product Analytics | ||
#514Write a query to calculate the percentage of users who complete each step of a multi-step feature setup flow. | Hard | Product Analytics | ||
#515Write a query to identify the top 5 features that are most commonly used within the 30 days before a user churns. | Hard | Product Analytics | ||
#516Write a query to track the growth rate of paid users week over week for the last 26 weeks. | Hard | Product Analytics | ||
#517Write a query to measure the effectiveness of the help center by comparing retention rates of users who visited it versus those who did not. | Hard | Product Analytics | ||
#518Write a query to calculate the frequency distribution of orders per user to understand the shape of purchasing behavior in your user base. | Hard | Product Analytics | ||
#519Write a query to identify users who have sent a high number of invitations but whose invitees have very low first-session engagement. | Hard | Product Analytics | ||
#520Write a query to measure the impact of a product changelog or release note on user engagement by comparing session counts before and after each release. | Hard | Product Analytics | ||
#521Write a query to find users who are on the free plan but have usage patterns that exceed the typical paid plan user in session depth. | Hard | Product Analytics | ||
#522Write a query to calculate the average revenue per daily active user (ARPDAU) for each month. | Hard | Product Analytics | ||
#523Write a query to identify users who consistently use the product on the same day of the week, indicating a habit-based usage pattern. | Hard | Product Analytics | ||
#524Write a query to compute the monthly expansion revenue from users who upgraded their plan. | Hard | Product Analytics | ||
#525Write a query to calculate the proportion of sessions that include at least one social or sharing action, as a measure of the product's virality potential. | Hard | Product Analytics | ||
#526Write a query to measure the time between a user's first session and their first support ticket submission. | Hard | Product Analytics | ||
#527Write a query to calculate the reactivation rate, defined as the percentage of churned users who return and start a new session within 90 days of cancelling. | Hard | Product Analytics | ||
#528Write a query to find the top 10 most common user behavior sequences that lead to a subscription upgrade within 24 hours. | Hard | Product Analytics | ||
#529Write a query to calculate the share of new users acquired through organic versus paid channels for each quarter. | Hard | Product Analytics | ||
#530Write a query to compute the weekly active user retention triangle, showing what percentage of each week's active users were also active in the next 1, 2, 3, and 4 weeks. | Hard | Product Analytics | ||
#531Write a query to measure the correlation between onboarding checklist completion percentage and 30-day session count. | Hard | Product Analytics | ||
#532Write a query to calculate the average number of touch points a user has with the product before reporting a bug. | Hard | Product Analytics | ||
#533Write a query to track the daily new, retained, resurrected, and churned user counts for each day in the last 30 days. | Hard | Product Analytics | ||
#534Write a query to identify which user segments have the highest LTV by combining acquisition source, plan type, and country. | Hard | Product Analytics | ||
#535Write a query to calculate the month-over-month change in average session length as a product quality trend indicator. | Hard | Product Analytics | ||
#536Write a query to build a feature interaction matrix showing which pairs of features are most commonly used in the same session. | Hard | Product Analytics | ||
#537Write a query to calculate the average monthly revenue per subscriber for each plan type and track how it changes over time. | Hard | Product Analytics | ||
#538Write a query to calculate the product's Blended CAC Payback Period by dividing the average customer acquisition cost by the average monthly gross profit per customer. | Hard | Product Analytics | ||
#539Write a query to find users who adopted a newly launched feature within the first 7 days of its release and measure their retention compared to users who did not. | Hard | Product Analytics | ||
#540Write a query to track the daily ratio of errors to total events as a product reliability metric. | Hard | Product Analytics | ||
#541Write a query to calculate the net promoter behavior score by measuring the ratio of users who refer others to users who submit complaints. | Hard | Product Analytics | ||
#542Write a query to measure how quickly the product reaches its engagement ceiling for new users by tracking the session in which average event count stops increasing. | Hard | Product Analytics | ||
#543Write a query to find users who have not used the product's newest feature but who use closely related legacy features heavily, making them ideal candidates for a targeted feature education campaign. | Hard | Product Analytics | ||
#544Write a query to calculate the 12-month predicted LTV for each user based on their revenue trajectory in the first 3 months. | Hard | Product Analytics | ||
#545Write a query to identify which day of the week and time of day has the highest session volume, to determine the optimal time for scheduled in-product events or announcements. | Hard | Product Analytics | ||
#546Write a query to compute the percentage of users who interact with at least 3 features within their first week of signing up. | Hard | Product Analytics | ||
#547Write a query to measure how many users complete the full upgrade flow from seeing the paywall to completing the upgrade, broken out by the feature that triggered the paywall. | Hard | Product Analytics | ||
#548Write a query to build a user segmentation model that classifies each user into champion, loyal, at-risk, lost, and new categories based on recency, frequency, and monetary value. | Hard | Product Analytics | ||
#549Write a query to calculate the 30-day moving average of the number of new feature requests submitted per day. | Hard | Product Analytics | ||
#550Write a query to measure the percentage of users who use the product on mobile only, desktop only, or both platforms. | Hard | Product Analytics | ||
#551Write a query to track the percentage of sessions that end with a user completing a key value action versus sessions that end without one. | Hard | Product Analytics | ||
#552Write a query to compute user growth efficiency, defined as the number of new paid users added per dollar of marketing spend, by month. | Hard | Product Analytics | ||
#553Write a query to measure how the product's engagement pattern changes around major holidays, comparing the two weeks surrounding each holiday to a two-week baseline period. | Hard | Product Analytics | ||
#554Write a query to identify which support ticket categories are most associated with users who churn within 30 days of submitting the ticket. | Hard | Product Analytics | ||
#555Write a query to track the weekly ratio of feature requests to bug reports as a proxy for product maturity and user satisfaction. | Hard | Product Analytics | ||
#556Write a query to compute the average number of sessions required before a user invites another user, to understand how product familiarity affects viral behavior. | Hard | Product Analytics | ||
#557Write a query to calculate the engagement rate for each notification campaign, defined as the percentage of recipients who take a target action within 24 hours of receiving the notification. | Hard | Product Analytics | ||
#558Write a query to produce a comprehensive product analytics summary for the most recent complete month, including DAU, MAU, stickiness, 30-day retention, new users, churn rate, and average revenue per user. | Hard | Product Analytics | ||
#559Write a query to calculate total revenue, average order value, and number of orders for each month in 2024. | Medium | Aggregations | ||
#560Write a query to compute the median order value for each product category. | Medium | Aggregations | ||
#561Write a query to calculate the revenue growth rate comparing each quarter in 2024 to the same quarter in 2023. | Medium | Aggregations | ||
#562Write a query to compute the 90th, 75th, 50th, and 25th percentile of session duration across all platforms. | Medium | Aggregations | ||
#563Write a query to calculate the Gross Merchandise Value (GMV) by month and the month-over-month percentage change. | Medium | Aggregations | ||
#564Write a query to calculate the average revenue per user (ARPU) broken down by plan type for each month. | Medium | Aggregations | ||
#565Write a query to count the number of users who made purchases in each revenue bucket: under $50, $50-$200, $200-$500, and over $500. | Medium | Aggregations | ||
#566Write a query to calculate the refund rate by month, defined as the percentage of completed order revenue that was subsequently refunded. | Medium | Aggregations | ||
#567Write a query to calculate the total number of events, sessions, and unique users for each day in the last 7 days as a daily activity summary. | Medium | Aggregations | ||
#568Write a query to calculate the average days between a user's first and second purchase for users who have made at least two purchases. | Medium | Aggregations | ||
#569Write a query to calculate the take rate for each product category in a marketplace, defined as the platform's revenue as a percentage of GMV. | Medium | Aggregations | ||
#570Write a query to calculate the average number of days a user is active per month over their first 6 months since signup. | Medium | Aggregations | ||
#571Write a query to calculate the total support cost per active user by month, using support ticket volume as a proxy for support cost. | Medium | Aggregations | ||
#572Write a query to calculate the Gross Profit Margin for each product category, using the cost and price columns in the products table. | Medium | Aggregations | ||
#573Write a query to calculate the count and percentage of users who have been active for more than 90 days, between 30 and 90 days, and fewer than 30 days. | Medium | Aggregations | ||
#574Write a query to compute the number of new subscriptions, cancellations, and net subscriber growth for each month. | Medium | Aggregations | ||
#575Write a query to calculate the average session count per user per week and segment users into frequency tiers. | Medium | Aggregations | ||
#576Write a query to calculate the revenue concentration using the Pareto principle: what percentage of users generate 80% of total revenue. | Medium | Aggregations | ||
#577Write a query to calculate the average revenue per order by device platform for the last 90 days. | Medium | Aggregations | ||
#578Write a query to measure the standard deviation of daily revenue for each month to understand revenue volatility. | Medium | Aggregations | ||
#579Write a query to calculate the number of active users for each combination of country and plan type. | Medium | Aggregations | ||
#580Write a query to compute the average number of events triggered per user in their first week, segmented by acquisition channel. | Medium | Aggregations | ||
#581Write a query to calculate the month-over-month change in average support ticket resolution time. | Medium | Aggregations | ||
#582Write a query to compute the 7-day rolling sum of new signups to create a smoothed acquisition trend. | Medium | Aggregations | ||
#583Write a query to compute the net MRR change each month, broken into new MRR, expansion MRR, contraction MRR, and churn MRR. | Medium | Aggregations | ||
#584Write a query to calculate the conversion rate from free trial to paid subscription, broken down by how many days the trial lasted before conversion. | Medium | Aggregations | ||
#585Write a query to compute the average number of features a user adopts per month over the first 3 months of their subscription. | Medium | Aggregations | ||
#586Write a query to calculate the percentage of total events generated by the top 10% most active users each month. | Medium | Aggregations | ||
#587Write a query to compute the average lifetime number of orders and total revenue for users who churned versus users who are still active. | Medium | Aggregations | ||
#588Write a query to calculate the daily percentage of sessions that result in at least one purchase event, as a session-level conversion rate. | Medium | Aggregations | ||
#589Write a query to calculate the average cart size (number of items per order) by product category. | Medium | Aggregations | ||
#590Write a query to calculate the weekly active user retention rate, defined as the percentage of last week's WAU who are also active this week. | Medium | Aggregations | ||
#591Write a query to measure the average number of page views per session for each country to identify markets with different browsing behaviors. | Medium | Aggregations | ||
#592Write a query to calculate the percentage of users who use the product exclusively on one platform versus users who switch between platforms. | Medium | Aggregations | ||
#593Write a query to calculate the average number of days between a user's signup and their first session on each platform. | Medium | Aggregations | ||
#594Write a query to compute the average session length for users on each day of their subscription. | Medium | Aggregations | ||
#595Write a query to calculate the number of users who have been continuously subscribed for 6, 12, and 24 months or longer. | Medium | Aggregations | ||
#596Write a query to calculate the average number of items in a user's wishlist or saved items at the time they make a purchase, as a measure of purchase consideration depth. | Medium | Aggregations | ||
#597Write a query to compute the total revenue lost to coupon and discount usage per month as a percentage of gross revenue. | Medium | Aggregations | ||
#598Write a query to calculate the average number of days between a user's first session and first purchase for each acquisition channel. | Medium | Aggregations | ||
#599Write a query to compute the monthly feature usage intensity score, defined as total uses divided by number of users who used each feature. | Medium | Aggregations | ||
#600Write a query to calculate the percentage of sessions that contain a search event and the average number of search events per session. | Medium | Aggregations | ||
#601Write a query to compute the cohort-level gross retention rate, defined as the percentage of cohort revenue from month 1 that is still being generated by the same cohort in month 6. | Medium | Aggregations | ||
#602Write a query to identify the top 5 events that are most predictive of a user churning by comparing event frequencies in the 30 days before churn versus the 30 days before the same point for retained users. | Medium | Aggregations | ||
#603Write a query to calculate the weekly new paying customer count and the rate of change week over week. | Medium | Aggregations | ||
#604Write a query to compute the average revenue per event, which normalizes revenue by the volume of user actions to measure monetization efficiency per unit of engagement. | Medium | Aggregations | ||
#605Write a query to calculate the average number of push notifications sent per active user per week and compare it to session frequency to measure notification saturation. | Medium | Aggregations | ||
#606Write a query to compute the average number of days between a user's last session and their subscription cancellation date. | Medium | Aggregations | ||
#607Write a query to calculate the percentage split of revenue between new customers and returning customers each month. | Medium | Aggregations | ||
#608Write a query to calculate the average product margin contribution by day of week to identify whether the product mix differs across days. | Medium | Aggregations | ||
#609Write a query to compute the average number of active users per account for team-based or B2B subscription products. | Medium | Aggregations | ||
#610Write a query to calculate the average time a user spends on each screen or page as a measure of content relevance and UX clarity. | Medium | Aggregations | ||
#611Write a query to calculate the share of orders that contain at least one discounted item versus orders with no discounts. | Medium | Aggregations | ||
#612Write a query to compute the monthly error rate per session and flag months where it exceeds the 3-month rolling average by more than 20%. | Medium | Aggregations | ||
#613Write a query to calculate the daily and weekly average revenue per paying user for the last 90 days. | Medium | Aggregations | ||
#614Write a query to identify seasonal revenue patterns by calculating the average revenue for each day of the year based on historical data. | Medium | Aggregations | ||
#615Write a query to calculate the number of users who return to the product each day within different time bands after their most recent prior session. | Medium | Aggregations | ||
#616Write a query to compute the monthly average order value trend for first-time buyers versus repeat buyers. | Medium | Aggregations | ||
#617Write a query to compute the percentage of total events that each event type represents, to create an event taxonomy ranking. | Medium | Aggregations | ||
#618Write a query to produce a single-row executive scorecard showing the most recent values of six core metrics: DAU, MAU, stickiness, 30-day retention, monthly revenue, and average order value. | Medium | Aggregations | ||
#619Write a query to calculate the conversion rate at each step of a three-step signup funnel: landing page visit, email submitted, and account created. | Hard | Funnel Analysis | ||
#620Write a query to measure the step-by-step drop-off rates in a five-step checkout funnel. | Hard | Funnel Analysis | ||
#621Write a query to calculate the overall funnel conversion rate from first session to first purchase, broken out by acquisition channel. | Hard | Funnel Analysis | ||
#622Write a query to build an onboarding funnel that tracks how many users complete each of four onboarding steps within their first 7 days. | Hard | Funnel Analysis | ||
#623Write a query to compare funnel conversion rates between mobile and desktop users across the checkout funnel. | Hard | Funnel Analysis | ||
#624Write a query to calculate the time each user takes to move through the checkout funnel steps, to identify which steps cause the most time-based friction. | Hard | Funnel Analysis | ||
#625Write a query to calculate the free-to-paid conversion funnel, showing how many free users progress from signup to feature limit hit to upgrade page view to paid subscription. | Hard | Funnel Analysis | ||
#626Write a query to calculate the weekly trend in checkout funnel conversion rates to detect whether conversion is improving or declining over time. | Hard | Funnel Analysis | ||
#627Write a query to build a feature adoption funnel showing how many users discover, try, and become regular users of a specific feature. | Hard | Funnel Analysis | ||
#628Write a query to measure the funnel drop-off between receiving a marketing email and completing a purchase, for each email campaign. | Hard | Funnel Analysis | ||
#629Write a query to calculate the percentage of users who complete each step of a mobile app onboarding flow within 24 hours of installation. | Hard | Funnel Analysis | ||
#630Write a query to calculate the abandonment rate at each step of a subscription upgrade flow, showing where free users stop progressing toward a paid plan. | Hard | Funnel Analysis | ||
#631Write a query to compare funnel conversion rates before and after a product change on a specific date. | Hard | Funnel Analysis | ||
#632Write a query to calculate the multi-touch attribution across the funnel by identifying how many funnel steps each converting user completed before purchasing. | Hard | Funnel Analysis | ||
#633Write a query to find users who entered the funnel but abandoned at a specific step and never returned to complete it. | Hard | Funnel Analysis | ||
#634Write a query to calculate the funnel conversion rate for each user cohort month, to track whether newer cohorts are converting through the activation funnel more efficiently. | Hard | Funnel Analysis | ||
#635Write a query to build a support ticket resolution funnel showing the flow from ticket submitted to first response to resolution to customer satisfaction rating. | Hard | Funnel Analysis | ||
#636Write a query to measure the funnel conversion rate from product page view to add-to-cart to purchase, broken down by product category. | Hard | Funnel Analysis | ||
#637Write a query to calculate the proportion of users who skip each optional step in an onboarding flow versus those who complete it. | Hard | Funnel Analysis | ||
#638Write a query to calculate the session-level funnel conversion for a search-to-purchase flow: search performed, result clicked, product detail viewed, added to cart, and purchased. | Hard | Funnel Analysis | ||
#639Write a query to measure how funnel drop-off rates differ between new users in their first 7 days versus established users with more than 30 days of tenure. | Hard | Funnel Analysis | ||
#640Write a query to track the daily funnel health by computing the number of users progressing through each stage of the core product loop. | Hard | Funnel Analysis | ||
#641Write a query to compute the funnel conversion rate for users who received a specific in-app guidance message versus those who did not. | Hard | Funnel Analysis | ||
#642Write a query to calculate the average number of sessions users have before they reach each key milestone in the product funnel. | Hard | Funnel Analysis | ||
#643Write a query to identify the most common exit event in each funnel stage, defined as the last event a user triggers before dropping out of the funnel. | Hard | Funnel Analysis | ||
#644Write a query to compute the time-to-complete metric for the full onboarding funnel, showing the distribution of how long users take from signup to first purchase. | Hard | Funnel Analysis | ||
#645Write a query to track the weekly funnel health score defined as a weighted average of conversion rates at each of three key steps. | Hard | Funnel Analysis | ||
#646Write a query to calculate funnel performance by user plan type, to understand whether free and paid users convert through the product usage funnel at different rates. | Hard | Funnel Analysis | ||
#647Write a query to build a re-engagement funnel showing how churned users respond to win-back campaigns, from email received to account reactivation to repeat purchase. | Hard | Funnel Analysis | ||
#648Write a query to produce a comprehensive funnel performance summary comparing the current month to the prior month across all key funnel stages. | Hard | Funnel Analysis | ||
#649Count how many users were assigned to each variant (control vs treatment) for experiment 'checkout_redesign'. | Hard | A/B Testing | ||
#650Find the total number of 'purchase' events made by users in each variant of experiment 'checkout_redesign'. | Hard | A/B Testing | ||
#651Calculate the conversion rate (users who made at least one 'purchase') for each variant of experiment 'checkout_redesign'. Conversion rate = converted users / total assigned users. | Hard | A/B Testing | ||
#652For experiment 'homepage_banner', find the average revenue per user in each variant. | Hard | A/B Testing | ||
#653List all users who were assigned to 'checkout_redesign' but never generated any event in the events table (i.e., no activity after assignment). | Hard | A/B Testing | ||
#654Find the number of days each experiment (experiment_id) has been running, based on the earliest and latest assigned_at timestamp. | Hard | A/B Testing | ||
#655Check for a Sample Ratio Mismatch (SRM): return the count and percentage split of users in control vs treatment for each experiment, so an analyst can visually confirm the split is close to 50/50. | Hard | A/B Testing | ||
#656Calculate conversion rate, using only events that happened after the user's assigned_at timestamp (to avoid counting pre-experiment activity as a conversion). | Hard | A/B Testing | ||
#657Compute the lift in conversion rate of treatment over control for experiment 'checkout_redesign', as a percentage: lift = (treatment_rate - control_rate) / control_rate * 100 | Hard | A/B Testing | ||
#658For experiment 'homepage_banner', break down conversion rate by variant and device_type, to check whether the effect differs by platform. | Hard | A/B Testing | ||
#659Find users who were assigned to more than one experiment simultaneously (overlapping experiments), which could bias results. Return user_id and the list of experiment_ids they overlapped in. | Hard | A/B Testing | ||
#660Calculate the average time (in hours) between assignment and first conversion event, for each variant. | Hard | A/B Testing | ||
#661For each day since the experiment started, compute a running (cumulative) conversion rate for each variant of 'checkout_redesign' — useful for plotting a trend line over the test duration. | Hard | A/B Testing | ||
#662Identify "guardrail metric" violations: for experiment 'checkout_redesign', find if the treatment group has a higher error/refund event rate than control (assume an event_name = 'refund'), which would be a bad sign even if conversion improved. | Hard | A/B Testing | ||
#663Compute the standard error of the conversion rate for each variant, using the formula: SE = SQRT( p * (1 - p) / n ) where p is the conversion rate and n is the number of users in that variant. | Hard | A/B Testing | ||
#664Using the standard errors from Q15, compute a z-score for the difference in conversion rates between treatment and control: z = (p_treatment - p_control) / SQRT( SE_treatment^2 + SE_control^2 ) | Hard | A/B Testing | ||
#665Segment the experiment results by country, and flag any country-level segment where the treatment effect direction is opposite to the overall effect direction (Simpson's-paradox style check). | Hard | A/B Testing | ||
#666Find users who were re-assigned to a different variant mid-experiment (i.e., appear more than once in experiment_assignments for the same experiment_id with different variant values) — a data quality/bucketing bug check. | Hard | A/B Testing | ||
#667Calculate revenue per user (RPU) and its variance for each variant, then compute the pooled standard error needed for a two-sample t-test comparing average revenue between control and treatment. | Hard | A/B Testing | ||
#668Build a query that returns one row per experiment with: total users, users per variant, conversion rate per variant, absolute lift, relative lift (%), and a TRUE/FALSE flag for whether the sample size in the smaller variant is at least 1,000 (a simple minimum-sample-size sanity check) — essentially a mini "experiment results summary" report. | Hard | A/B Testing | ||
#669Uber is seeing a decline in driver supply in three major cities. Write a query to diagnose whether the drop is driven by fewer new driver signups, increased driver churn, or reduced hours per active driver. | Hard | Business Cases | ||
#670Airbnb wants to understand why revenue per available listing has declined in Paris over the last quarter. Write a query to decompose the decline into occupancy rate changes, pricing changes, and listing mix shifts. | Hard | Business Cases | ||
#671Meta wants to measure the health of the Facebook Groups product. Write a query that computes group-level engagement health scores combining post creation, comment activity, and membership growth. | Hard | Business Cases | ||
#672Amazon wants to identify which product categories have the most fraud risk based on unusually high return rates combined with short time between purchase and return request. Write the query. | Hard | Business Cases | ||
#673Google wants to understand whether Search users who also use Google Maps have higher long-term retention than Search-only users. Write a query that isolates the causal effect of cross-product usage from the selection effect of power users being more likely to use multiple products. | Hard | Business Cases | ||
#674Stripe wants to reduce involuntary churn caused by failed payments. Write a query to identify which merchant accounts have the highest failed payment rates and what card brands or failure reasons are most common. | Hard | Business Cases | ||
#675Netflix wants to understand which content genres drive the highest subscriber retention to inform content acquisition budget allocation. Write the query. | Hard | Business Cases | ||
#676DoorDash wants to identify restaurants where order quality problems are causing repeat order rates to decline. Write a query to find restaurants with declining second-order rates and correlate them with complaint data. | Hard | Business Cases | ||
#677Spotify wants to understand whether its podcast recommendation algorithm is working. Write a query comparing the engagement and retention of users who discovered a new podcast through recommendations versus those who discovered it through search. | Hard | Business Cases | ||
#678LinkedIn wants to measure the ROI of its Premium subscription features by comparing the job application success rate of Premium versus free users. Write the query. | Hard | Business Cases | ||
#679Airbnb's trust and safety team suspects that some hosts are creating fake reviews. Write a query to identify hosts with statistically suspicious review patterns. | Hard | Business Cases | ||
#680Amazon Prime wants to understand which Prime benefits drive the highest renewal intent. Write a query comparing renewal rates for members who used each type of Prime benefit in the 30 days before renewal. | Hard | Business Cases | ||
#681Shopify wants to identify which merchant segments are most at risk of churning their subscription in the next 60 days. Write a query that builds a churn risk score for each merchant. | Hard | Business Cases | ||
#682Twitter wants to understand which types of content cause users to unfollow accounts or mute keywords. Write a query that identifies content categories and posting behaviors most associated with unfollows and mutes in the last 30 days. | Hard | Business Cases | ||
#683Write a complete SQL business case analysis for a SaaS company that wants to decide whether to invest in improving its mobile app. The analysis should cover mobile usage share, mobile vs desktop retention, mobile conversion rate, and the revenue impact of closing the mobile conversion gap. | Hard | Business Cases | ||
#684The following query is supposed to calculate the 7-day retention rate for new users, but it returns a retention rate over 100% for several weeks. Find and fix the bug. | Hard | Performance | ||
#685The following query is supposed to show the month-over-month revenue growth rate but returns NULL for every row in the growth_pct column. Find and fix the bug. | Hard | Performance | ||
#686The following query runs correctly but takes 4 minutes to execute on a table with 50 million events. Identify the performance problems and rewrite it to run significantly faster. | Hard | Performance | ||
#687The following query is supposed to return only users who made a purchase but have NOT submitted a support ticket in the last 90 days. It is returning the wrong results, including users who do have recent support tickets. Find and fix the bug. | Hard | Performance | ||
#688The following query calculates the percentage of revenue contributed by each product category but the percentages do not add up to 100%. Identify all bugs and produce a corrected version that handles NULL categories, partial refunds, and rounding correctly. | Hard | Performance |
Ready for Your PM Interview?
Apply your SQL skills to real opportunities at top companies.
Why SQL Matters for Product Managers
SQL is an essential skill for modern Product Managers. While you don't need to be a data engineer, understanding how to query databases enables you to make data-driven decisions without depending on analysts.
Key SQL Concepts for PMs
Basics: Understanding SELECT, WHERE, and basic filtering is fundamental. These allow you to pull specific data points for quick analysis.
Joins: Combining data from multiple tables is crucial for understanding user behavior across different touchpoints—from signup to purchase to retention.
Aggregations: GROUP BY and HAVING let you segment users, calculate metrics like DAU/MAU, and identify patterns in your data.
Window Functions: These are game-changers for analytics—running totals, cohort analysis, ranking users, and comparing periods become straightforward.
Master these concepts, and you'll be able to answer critical product questions independently. Browse our PM jobs to find opportunities where these skills are valued.