SQL for Data Analytics: Part-1
Common Business Questions and how to get these answered using SQL. I have used postgresql (open source database) for this exercise and added screenshot of solution without going into details of SQL logic/syntax.
It is assumed that you have understanding of how CTE (common table expression) and Joins
Say we have a table Activation in this form,
#1: Monthly New customer count
#2: Monthly Repeated customer count: Repeated customer is someone who was active this month and any previous month.
#3: Monthly Retained customer count: Retained customer is someone who was active this month and immediate previous month.
This can also be solved by CTE (common table expression) similar to Repeated customer count.
#4: Monthly Churned customer count: Churned customer is someone who was active last month but did not come back this month.
#5: Monthly Reactive customer count: Reactive customer is someone who was active previous month(except last month) and come back this month.
#6: Month over Month Percent Change for active users
This can also be solved using lag() window function.
Do share in comment if you have an alternate method to solve the above Questions.
If you are a #AdobeAnalytics enthusiast, do join our group AdobeAnalyticsProfessionalAndRecruiters .
If you enjoyed reading check out my other articles,
Visitor Retention Analysis using Adobe Analytics
Anomaly Detection in Non-programming way using Excel
#AdobeAnalytics #DigitalAnalytics #WebAnalytics #Analytics
#continouslearning #learningeveryday
