Operation Analytics and Investigating Metric Spike�

Project 3

select job_id, ds as dates, round(count(job_id)/sum(time_spent)*3600) as 'job reviewed per hour per day' from operation.`sql-data-set-1`group by 2,1;

Number of jobs reviewed: Amount of jobs reviewed over time.�My task: Calculate the number of jobs reviewed per hour per day for November 2020?

​

SYNTAX:

select ds as dates,event, round(count(event)/sum(time_spent),2) as daily_throughput from operation.`sql-data-set-1`group by 1,2 order by 1;

Throughput: It is the no. of events happening per second.�My task: Let’s say the above metric is called throughput. Calculate 7 day rolling average of throughput? For throughput, do you prefer daily metric or 7-day rolling and why?

​

​

SYNTAX:

select time_spent,language as languages ,round (( (100*count(distinct(language))/sum(time_spent)))) as percentage from operation.`sql-data-set-1`group by 1,2;

Percentage share of each language: Share of each language for different contents.�My task: Calculate the percentage share of each language in the last 30 days?

​

SYNTAX:

select job_id,count(*) as duplicate_countsfrom operation.`sql-data-set-1`group by job_id having count(*) >1

Duplicate rows: Rows that have the same value present in them.�My task: Let’s say you see some duplicate rows in the data. How will you display duplicates from the table?

​

SYNTAX:

Case Study 2 (Investigating metric spike)

select monthnum, usercount, round(((usercount/lag(usercount,1) over (order by monthnum) -1)*100),2) as 'growth%' from(select extract( month from created_at) as monthnum,count(activated_at) as usercountfrom operation.`table-1 users - table-1 users`where activated_at not in ("")group by monthnumorder by monthnum ) a;

User Engagement: To measure the activeness of a user. Measuring if the user finds quality in a product/service.�My task: Calculate the weekly user engagement?

SYNTAX:

select firstloginweek as "weeknumber",sum(case when week_number=0 then 1 else 0 end) as "week 0",sum(case when week_number=1 then 1 else 0 end) as "week 1",sum(case when week_number=2 then 1 else 0 end) as "week 2",sum(case when week_number=3 then 1 else 0 end) as "week 3",sum(case when week_number=4 then 1 else 0 end) as "week 4",sum(case when week_number=5 then 1 else 0 end) as "week 5",sum(case when week_number=6 then 1 else 0 end) as "week 6",sum(case when week_number=7 then 1 else 0 end) as "week 7",sum(case when week_number=8 then 1 else 0 end) as "week 8",sum(case when week_number=9 then 1 else 0 end) as "week 9",sum(case when week_number=10 then 1 else 0 end) as "week 10",sum(case when week_number=11 then 1 else 0 end) as "week 11",sum(case when week_number=12 then 1 else 0 end) as "week 12",sum(case when week_number=13 then 1 else 0 end) as "week 13",sum(case when week_number=14 then 1 else 0 end) as "week 14"from( select a.user_id, a.loginweek,b.firstloginweek,a.loginweek-b.firstloginweek as week_numberfrom(select user_id,extract( week from occurred_at) as loginweek from operation.`table-2 events - table-2 events` group by 1,2) a,(select user_id, min(extract(week from occurred_at) ) as firstloginweekfrom operation.`table-2 events - table-2 events` group by 1) bwhere a.user_id=b.user_id) cgroup by firstloginweek order by firstloginweek;

Weekly Retention: Users getting retained weekly after signing-up for a product.�My task: Calculate the weekly retention of users-sign up cohort?

SYNTAX:

select extract( week from occurred_at) as week,count( case when action = 'sent_weekly_digest' then user_id else null end) as weekly_digest,count( case when action = 'email_open' then user_id else null end) as email_open,count( case when action = 'email_clickthrough' then user_id else null end) as email_clickthrough,count( case when action = 'sent_reengagement_email' then user_id else null end) as sent_reengagement_email,count(user_id) as total from operation.`table-3 email_events - table-3 email_events` group by 1;

Email Engagement: Users engaging with the email service.�My task: Calculate the email engagement metrics?

SYNTAX:

select extract(week from occurred_at) as week,count( device) as device_num,count( distinct user_id) as user_num,round(count(device)/count(distinct user_id)) as device_per_userfrom operation.`table-2 events - table-2 events`where event_type ='engagement'group by week;

User Growth: Amount of users growing over time for a product.�My task: Calculate the user growth for product?

�SYNTAX:

select extract( week FROM occurred_at) as week_num ,count(distinct user_id) as weekly_active_users from operation.`table-2 events - table-2 events`where event_type ='engagement'group by week_num;

Weekly Engagement: To measure the activeness of a user. Measuring if the user finds quality in a product/service weekly.�My task: Calculate the weekly engagement per device?

​

SYNTAX: