Operation Analytics and Investigating Metric Spike

Advanced SQL

Difficulty Level: Advanced

Description:

Operational Analytics plays a pivotal role in understanding and optimizing a company's end-to-end operations. As a Data Analyst, you'll collaborate with various teams like operations, support, and marketing, leveraging data insights to drive improvements across the organization.

Investigating metric spikes is a critical aspect of Operational Analytics, involving the exploration of sudden changes in key metrics such as user engagement or sales. In this project, you'll step into the shoes of a Lead Data Analyst at a company like Microsoft, tasked with analyzing datasets to provide actionable insights into metric spikes and operational efficiencies.

Case Study 1: Job Data Analysis

Dataset:

• Job Data Dataset

Tasks:

1. Jobs Reviewed Over Time:

   • Objective: Calculate the number of jobs reviewed per hour for each day in November 2020.
   • Your Task: Write an SQL query to calculate the number of jobs reviewed per hour for each day in November 2020.

2. Throughput Analysis:

   • Objective: Calculate the 7-day rolling average of throughput (number of events per second).
   • Your Task: Write an SQL query to calculate the 7-day rolling average of throughput. Additionally, explain your preference between daily metric and 7-day rolling average for throughput.

3. Language Share Analysis:

   • Objective: Calculate the percentage share of each language in the last 30 days.
   • Your Task: Write an SQL query to calculate the percentage share of each language over the last 30 days.

4. Duplicate Rows Detection:

   • Objective: Identify duplicate rows in the data.
   • Your Task: Write an SQL query to display duplicate rows from the job_data table.

Case Study 2: Investigating Metric Spike

Dataset:

• Second Dataset

Tasks:

1. Weekly User Engagement:

   • Objective: Measure the activeness of users on a weekly basis.
   • Your Task: Write an SQL query to calculate the weekly user engagement.

2. User Growth Analysis:

   • Objective: Analyze the growth of users over time for a product.
   • Your Task: Write an SQL query to calculate the user growth for the product.

3. Weekly Retention Analysis:

   • Objective: Analyze the retention of users on a weekly basis after signing up for a product.
   • Your Task: Write an SQL query to calculate the weekly retention of users based on their sign-up cohort.

4. Weekly Engagement Per Device:

   • Objective: Measure the activeness of users on a weekly basis per device.
   • Your Task: Write an SQL query to calculate the weekly engagement per device.

5. Email Engagement Analysis:

   • Objective: Analyze how users are engaging with the email service.
   • Your Task: Write an SQL query to calculate the email engagement metrics.

Additional Resources:

• Project Report PDF
• LinkedIn Profile
• Email: [email protected]
• GitHub: @Shraaj1

---