SQL - Advanced Analytical Queries
Cohort analysis, funnels, retention, KPI calculations, pivot queries, optimization
1In a cohort analysis, what is the main role of the first touch date?
In a cohort analysis, what is the main role of the first touch date?
Answer
The first touch date allows grouping users into cohorts based on when they first interacted with the product. This temporal segmentation is essential for comparing the behavior of user groups acquired at different periods and identifying trends or performance changes over time.
2Which window function should be used to calculate the number of days between a user's first and last activity?
Which window function should be used to calculate the number of days between a user's first and last activity?
Answer
The FIRST_VALUE and LAST_VALUE functions with an OVER clause allow retrieving the first and last values of a column within a defined window respectively. Combined with DATEDIFF or date subtraction, they enable calculating a user's active lifespan without requiring multiple subqueries.
3How to build a monthly retention table with SQL using cohorts based on the signup month?
How to build a monthly retention table with SQL using cohorts based on the signup month?
Answer
Building a retention table requires first identifying each user's cohort via DATE_TRUNC on the signup date, then calculating the number of months elapsed between the cohort and each activity. The GROUP BY on these two dimensions and a COUNT DISTINCT of active users provide the complete table.
To analyze a 4-step conversion funnel (visit → signup → activation → purchase), which SQL approach is most robust?
How to calculate the monthly churn rate in SQL for a subscriber base?
+17 interview questions
Other Data Analytics interview topics
Google Sheets - Fundamentals
Google Sheets - Advanced Formulas
SQL - Fundamentals
SQL - Aggregations and Grouping
SQL - Joins
BigQuery - Fundamentals
Data Cleaning
KPIs and Business Metrics
Descriptive Statistics
Zapier and No-Code Automation
Data Visualization Principles
Python & Pandas - Fundamentals
Google Sheets - Automated Dashboards
SQL - Subqueries and CTEs
SQL - Window Functions
BigQuery - Advanced Features
Data Modeling
Funnel and Conversion Analysis
Cohort and Retention Analysis
Google Tag Manager and Tracking
APIs and Webhooks
dbt - Fundamentals
AB Testing and Applied Statistics
Looker Studio (Google Data Studio)
Power BI - Fundamentals
dbt - Advanced Features
Power BI - DAX and Advanced Dashboards
Python Analytics - Advanced Analysis and ML
Master Data Analytics for your next interview
Access all questions, flashcards, technical tests, code review exercises and interview simulators.
Start for free