Need help in assignment

  • Responded_Pro_Booking - is an ID and is also referred to as Pro/ Professional who are delivering services.
  • Customer_Request_ID - is an ID and is also referred to as one Job done by the professional/Pro.

Question 1 : Get Month wise New & Old Pro(Responded_Pro_Booking) count

New Pro = First Job(Customer_Request_Id) in the month (only consider 2020)

Old Pro = First Job(Customer_Request_Id) in past months (only 2020)

For Example-

New Pro for the Month May - is the Pro(Responded_Pro_Booking) which has his first job(Customer_Request_Id) in the month of the May

Old Pro- All the Pros having done jobs till that month(May).

Question 2: Get month wise churn %

Churn % = Pro not delivered in next month out of the pros active in the current month/pros active in the current month.

Question 3: Get month wise pro state wise churn %

Pro State - Old (Reactive / Repeat ) New

New Pro = First Job in the month (only consider 2020)

Old Pro = First Job in some month in past (only 2020)

Old pro count = repeat pro count + reactive pro count

Repeat pro has deliveries in previous month & current month.

Reactive pro has delivered some time in the past

& current month.

ANS- select date_trunc(‘month’, timestamp) as date,
count(*) as count
from events
where event_name = ‘created chart’
group by 1
order by 1

QUE -2
ANS - with monthly_activity as (
select distinct
date_trunc(‘month’, created_at) as month,
user_id
from events
)
select
this_month.month,
count(distinct user_id)
from monthly_activity this_month
join monthly_activity last_month
on this_month.user_id = last_month.user_id
and this_month.month = add_months(last_month.month,1)
group by month