SQL for Data Analytics

SQLDATA ANALYTICS

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.

15+ years of IT work experience as Technical delivery Lead, Analytics Architect, AEP/CJA Implementation Consultant.

Adobe certified expert in Adobe Analytics, Adobe Target, Adobe Experience Platform (AEP), Real-Time Customer Data Platform (RT-CDP), Customer Journey Analytics (CJA), Journey Optimizer (AJO). Well versed with Google Analytics Server-side, Conversion API (CAPI), Privacy & Consent Management (OneTrust).

Deputed to Canada, USA, Netherlands, Germany, UK to work closely with business clients, business analysts, solution architects, solution designers, and other key stakeholders. Passionate to decode the online consumer behaviour by using an analytics data-driven approach.