RETENTION ANALYSIS

1.0 Basics of Retention analysis What is Retention Analysis? User retention is a key metric used across various businesses to measure the ratio of users who come back in specific time interval to use a product or service. For example, if a company like Netflix acquires some X number of subscribers in a month, then … Read more

Stitch Fix SQL Interview Question

Stitch Fix SQL Interview Question

Question:  You have been given the below table on transactions from users. Write a query to get the number of users and total products bought per latest transaction date where each user is bucketed into their latest(last) transaction date. Explanations & Definition: every user will have a last(lastest) transaction date, also multiple users can have the … Read more

Disney SQL Interview Question

disney sql interview

Question: Assume you are given the below table on watch times (in minutes) for all users, where each user is based in a given city. Write a query to return all pairs of cities with total watch times within 10000 minutes of one another. Difficulty Level: Easy, Source: Nicksingh, Tag: Disney SQL Interview Question Assumptions: No NULLS present; DBMS: … Read more

Opendoor SQL Interview Question

Opendoor SQL Interview Question

Question: Assume you are given the below table on house prices of listed properties from various zip codes. Write a query to get the top 5 zip codes by market share of house prices for all zip codes. Also return the corresponding market share (in %, 2 decimal) as well and filter zip codes with … Read more

Amazon SQL Interview Question

amazon sql interview question

Question: Assume you are given the below table on purchases from users. Write a query to get the number of users that purchased at least one product on multiple days. Explanation & Definition: basically you are required to find those users having more than 1 purchase dates. Difficulty Level: Easy, Source: NickSingh, Tag: Amazon SQL Interview Question Assumptions: No … Read more

META SQL Interview Question

META SQL Interview Question

Question: Assume you are given the below tables on users and user posts. Write a query to get the distribution of the number of posts per user for each given day. Explanation & Definition: Each user will have a certain post count for each day, by daily distribution we mean for each day we need the … Read more

UBER SQL Interview Question

UBER SQL Interview Question

Question: Assume you are given a table for spending activity by product type. Write a query to calculate the cumulative spend for each product over time (days) in chronological order. Difficulty Level: Medium, Source: NickSingh.com, Tag: UBER SQL Interview Question Note: Cumulative sum is the same as running totals, which is used to display the data as it grows … Read more

Facebook SQL Interview Question

META SQL Interview Question

Question: Assume you have the below ‘events’ table on app analytics. Write a query to get the click-through rate (CTR) per app in 2019. Difficulty Level: Easy, Source: NickSingh.com, Tag: Facebook SQL Interview Question Note: CTR is the number of clicks that your ad receives divided by the number of times your ad is shown. i.e CTR = total_clicks/total_ads_served … Read more