Checking user retention and churn in SQL

InterviewQs

Checking user retention and churn in SQL

SQL Database Querying Window Function Subquery Self-Join

Forward this email to a friend or colleague and challenge them to solve it.

Hi, 

You're given the following table showing user logins to your system:

Table: login_info

user_id month year
1 1 2021
2 1 2021
3 1 2021
4 1 2021
1 2 2021
2 3 2021
3 2 2021

Using the table above:

  • Write a SQL query that returns the number of retained users per month in 2021. In this case, we'll define retention for each given month as the number of users who logged in that month and also logged in the prior month.
  • Once you've written that query, write one additional query to return the users who did not come back in a given month (e.g. they logged in the month prior, but not the next consecutive month) -- we'll call these our churned users.

You can view/query the data in an interactive SQL fiddle here.


 

You’ve accumulated 341 questions since joining our email list. Create a premium account to access detailed solutions to each problem (including this one) + our new Case studies.


Was this email forwarded to you? You can join our list here to receive questions

How did you like today's question?

15411 Boischatel, Montreal, QC, H9H1Y7, Canada
Don't want to get these emails anymore? We'll forgive you if you unsubscribe

Older messages

Price per calorie of various snack foods

Friday, September 2, 2022

Data Analysis, Python, Pandas, Data Manipulation, InterviewQs Price per calorie of various snack foods Data Analysis Python Pandas Data Manipulation Forward this email to a friend or colleague and

Bubble sort algorithm

Wednesday, August 31, 2022

Sorting, Algorithim, Data Structures, InterviewQs Bubble sort algorithm Sorting Algorithim Data Structures Forward this email to a friend or colleague and challenge them to solve it. Hi, Bubble sort (

Covariance and correlation

Monday, August 29, 2022

Statistics, Covariance, Correlation, InterviewQs Covariance and correlation Statistics Covariance Correlation Forward this email to a friend or colleague and challenge them to solve it. Hi, Explain the

Checking interview scores for accepted candidates

Friday, August 26, 2022

SQL, Database Querying, InterviewQs Checking interview scores for accepted candidates SQL Database Querying Forward this email to a friend or colleague and challenge them to solve it. Hi, Suppose your

Correlation between aces and points won in tennis

Wednesday, August 24, 2022

Data Analysis, Python, Pandas, External Dataset, Data Manipulation, Data Visualization, Statistics, Pearson's R, Chi-squared Test, InterviewQs Correlation between aces and points won in tennis Data

You Might Also Like

The clock’s ticking for TikTok

Wednesday, April 24, 2024

The US Senate has passed a bill that would ban TikTok if its US business is not divested by Bytedance View this email online in your browser By Alex Wilhelm Wednesday, April 24, 2024 Good morning, and

How to block Windows 11 Start menu ads

Wednesday, April 24, 2024

Oura Ring hits Target; 7 iPad Pro features we need; AI hallucinations aren't all bad -- ZDNET ZDNET Tech Today - US April 24, 2024 placeholder Microsoft is now showing ads in Windows 11's Start

The Chilling of TikTok

Wednesday, April 24, 2024

Ban or not, this is the end of TikTok as we know it The Chilling of TikTok By MG Siegler • 24 Apr 2024 View in browser View in browser The tok is tiking... Later today, President Biden will sign a bill

GenAI is transforming materials design

Wednesday, April 24, 2024

‌ ‌ ‌ ‌ ‌ ‌ ‌ ‌ ‌ ‌ ‌ ‌ ‌ ‌ ‌ ‌ ‌ ‌ ‌ ‌ ‌ ‌ ‌ ‌ ‌ ‌ ‌ ‌ ‌ ‌ ‌ ‌ ‌ ‌ ‌ ‌ ‌ ‌ ‌ ‌ ‌ ‌ ‌ ‌ ‌ ‌ ‌ ‌ ‌ ‌ ‌ ‌ ‌ ‌ ‌ ‌ ‌ ‌ ‌ ‌ ‌ ‌ ‌ ‌ ‌ ‌ ‌ ‌ ‌ ‌ ‌ ‌ ‌ ‌ ‌ ‌ ‌ ‌ ‌ ‌ ‌ ‌ ‌ ‌ ‌ ‌ ‌ ‌ ‌ ‌ ‌ ‌ ‌ ‌ ‌ ‌ ‌ ‌ ‌ ‌ ‌

⚙️ Meta Smart Glasses

Wednesday, April 24, 2024

Plus: $3B valuation for AI startup ‌ ‌ ‌ ‌ ‌ ‌ ‌ ‌ ‌ ‌ ‌ ‌ ‌ ‌ ‌ ‌ ‌ ‌ ‌ ‌ ‌ ‌ ‌ ‌ ‌ ‌ ‌ ‌ ‌ ‌ ‌ ‌ ‌ ‌ ‌ ‌ ‌ ‌ ‌ ‌ ‌ ‌ ‌ ‌ ‌ ‌ ‌ ‌ ‌ ‌ ‌ ‌ ‌ ‌ ‌ ‌ ‌ ‌ ‌ ‌ ‌ ‌ ‌ ‌ ‌ ‌ ‌ ‌ ‌ ‌ ‌ ‌ ‌ ‌ ‌ ‌ ‌ ‌ ‌ ‌ ‌ ‌ ‌

Endpoint Security Tips Curated by Experts - Get This Guide Now

Wednesday, April 24, 2024

Endpoint Security Tips Curated by Experts Hey there, It's no secret that endpoints are prime targets for hackers—so how can you defend them better? Well, we have some suggestions for where you can

Senate passes the bill that could ban TikTok

Wednesday, April 24, 2024

The Morning After It's Wednesday, April 24, 2024. The Senate approved a measure that will require ByteDance to sell TikTok or face a ban, in a vote of 79 to 18. The Protecting Americans from

[Incubator] Dates for our next Student Orientation and Demo Day

Wednesday, April 24, 2024

Also, here's the link to our last student demo day. ‌ ‌ ‌ ‌ ‌ ‌ ‌ ‌ ‌ ‌ ‌ ‌ ‌ ‌ ‌ ‌ ‌ ‌ ‌ ‌ ‌ ‌ ‌ ‌ ‌ ‌ ‌ ‌ ‌ ‌ ‌ ‌ ‌ ‌ ‌ ‌ ‌ ‌ ‌ ‌ ‌ ‌ ‌ ‌ ‌ ‌ ‌ ‌ ‌ ‌ ‌ ‌ ‌ ‌ ‌ ‌ ‌ ‌ ‌ ‌ ‌ ‌ ‌ ‌ ‌ ‌ ‌ ‌ ‌ ‌ ‌ ‌ ‌

Warning: Security Flaws Exposed Keystrokes of Over 1 Billion Chinese Keyboard App Users

Wednesday, April 24, 2024

THN Daily Updates Newsletter cover Webinar -- Uncovering Contemporary DDoS Attack Tactics -- and How to Fight Back Stop DDoS Attacks Before They Stop Your Business... and Make You Headline News.

Post from Syncfusion Blogs on 04/24/2024

Wednesday, April 24, 2024

New blogs from Syncfusion What's New in React Query Builder: 2024 Volume 1 By Satheeskumar S This blog explores the new features added to the Syncfusion React Query Builder in the 2024 Volume 1