Excel also helps us to look into such scenarios using the  in-built what-if analysis feature.

• With What-If Analysis, you try out different possible scenarios and see how they affect your outcomes. 

• It helps you prepare for different situations so you’re not surprised by what happens.

Note: This feature is not present in Excel online free version. 

Three types:

• Goal Seek

• Scenario Manager

• Data table

Goal: 

Determine the insurance coverage needed in each region to keep the total amount that patients pay out-of-pocket below a set limit.

Example:

What insurance coverage percentage is required in each region to ensure out-of-pocket expenses stay below $100,000?

Data table:

• How 1 or 2 input can impact 

• Result in a table showing all possible combinations.

Scenario Manager:

• Take in multiple inputs

• Result: Summary comparing results of different scenarios

• A window function allows us to perform calculations across a specific set of rows related to the current row. 

• calculations happen within a defined window of data

• useful for aggregates, rankings, and cumulative totals without altering the dataset.

• The OVER clause is key to defining this window. 

SELECT column_name1, 

 window_function(column_name2)

 OVER([ORDER BY column_name3]) AS new_column

FROM table_name;

The PARTITION BY clause is an extension of the OVER() clause that divides the result set into logical partitions, allowing you to isolate calculations within each partition without collapsing the original rows.

Syntax:

The ROW_NUMBER() function is perfect when you need to uniquely rank rows within a result set, providing a simple, efficient way to add row numbers.

Syntax:

RANK() is a ranking function that assigns ranks to rows within a partition of a result set. It allows ties by giving multiple rows the same rank if they have the same value in the ORDER BY clause. 

Syntax

SELECT 

    r.restaurant_id,

    r.name AS restaurant_name,

    CASE 

        WHEN r.restaurant_id IN (96814, 18424018) THEN 50000  -- Force a tie for testing

        ELSE SUM(o.total_price)

    END AS total_revenue,

    RANK() OVER (ORDER BY 

        CASE 

            WHEN r.restaurant_id IN (96814, 18424018) THEN 50000  -- Same total for testing

            ELSE SUM(o.total_price)

        END DESC

    ) AS rownumber

FROM Restaurants r

JOIN Orders o ON r.restaurant_id = o.restaurant_id

GROUP BY r.restaurant_id, r.name

ORDER BY total_revenue DESC;

DENSE_RANK() is a ranking function similar to RANK(), but it does not leave gaps in the ranking sequence when there are ties. 

It is ideal when you want a continuous ranking order, even when multiple rows share the same rank.

Syntax:

NTILE() is a ranking function that distributes rows into a specified number of roughly equal groups, called tiles or buckets. 

It is particularly useful for percentile calculations and cohort analysis.

Syntax

A CTE (Common Table Expression) is a temporary result set in SQL that you can reference within a SELECT, INSERT, UPDATE, or DELETE statement.

CTEs are often used to simplify complex joins, improve code readability, and enhance query performance.

Simple CTE:

WITH cte_name AS (

    SELECT ...

)

SELECT *

FROM cte_name;

Chaining Multiple CTE:

WITH first_cte AS (

    SELECT ...

),

second_cte AS (

    SELECT ... FROM first_cte

)

SELECT *

FROM second_cte

Aspect

Subqueries

CTEs

Readability

Harder with nested levels

Easier to read and modular

Reusability

Not reusable

CTEs can be referenced multiple times

Debuggability

Difficult

Easier to debug each CTE block

Performance

May be faster for simple cases

Comparable; may optimize better for complex logic

Use Case Suitability

Best for simple, one-off operations

Best for layered, complex logic

We want to Analyze the factors affecting sales. For example:

• Month with lowest and highest sales?

• How sales vary across regions?

• How sales vary for different brands?

•          How sales vary for different brands and different months?

•          How sales vary for different brands and different regions?

• How sales vary with different retailers? ….. etc

As a part of the project submissions, we are not limiting you to do the project in a certain way. You are free to do the project on your own and come up with your own analysis. Here are a few guidelines to submit your project.

1. Video Demo of your Project

   1. Record a video presenting your project work. You can use any recording platforms like Loom or  OBS Studio. You can also record the video in Zoom, download and submit it. Make sure the video length does not exceed 15 minutes. Video recording is supported on Crio Platform but you won’t be able to share screens.

   2. Upload the video in the video activity given.

Instructions for your project submission - Link

2. Submit the GitHub repository link in the activity

   1. Follow this process to create a GitHub account, push the project and submit the repository link - Process document

For the coke-sales analysis

• What are the different analysis which can be done?

• How to approach each analysis?

Don’t we need a template to synchronise our thought-process in a systematic way?

? 1. Understanding Problem Statement

? 2. What Data Exists?

? 3. Data Points Related to the Problem Statement

? 4. Analysis and Visualization

? 5. Insights & Next Steps

• Google Colab is a free, cloud-hosted Jupyter notebook environment provided by Google.

• Enables you to run Python code directly in the browser, no installation needed.

• People have shared a wide range of feedback about ChatGPT through ratings and written reviews

• In this session, you’ll explore these reviews to understand what users love, what frustrates them, and what can be improved

• By analyzing sentiment, patterns over time, and common issues, we can learn how to make ChatGPT better.

The key objectives of this problem are:

• Sentiment Analysis: 

Identify positive, negative, and neutral user sentiments

• Issue Identification:

Detect common problems driving negative reviews

• Time-Series Analysis:

Track how sentiment evolves over time

The dataset contains 4 fields:

• Review ID: A unique identifier for every review

• Review: Comments given by the user

• Rating: Represents the levels of satisfaction on a scale of 0-5

• Review date: When the review was posted

Activity: Building Data Intuition with Public Logistics Power BI Dashboards

Objective: To develop data intuition by observing existing Power BI dashboards from the logistics domain, making inferences about the underlying data, business processes, and potential insights.

Public URL: https://community.fabric.microsoft.com/t5/Data-Stories-Gallery/Transportation-Analytics/m-p/3734629 

1. Observe the Dashboard 

• What are the main charts, graphs, and visualizations?

• What KPIs (Key Performance Indicators) are being displayed? (e.g., On-time Delivery Rate, Fuel Consumption, Inventory Levels, Shipment Volume).

• How is the data segmented or filtered? (e.g., by region, date, product, carrier).

• Are there any interactive elements ?

2. Make Inferences about the Data

• What kind of data would be needed to create this dashboard? (e.g., historical delivery records, vehicle telemetry data, warehouse inventory logs, customer order details).

• What data sources might be involved? (e.g., ERP systems, TMS, WMS, GPS tracking, sensor data).

• What's the granularity of the data? (e.g., daily, per shipment, per vehicle, per item).

3. Infer Business Questions & Insights

• What business questions is this dashboard trying to answer? (e.g., "Are we meeting our delivery SLAs?", "Which routes are most fuel-efficient?", "Do we have too much stock of a particular item?", "Which suppliers are performing best?").

• What immediate insights can you draw from the current state of the dashboard? (e.g., "On-time delivery dropped significantly last month," "Warehouse B has consistently higher picking errors," "Fuel costs are rising in the North region").

• How might a logistics manager use this dashboard to make decisions?

4. Identify Potential Improvements/Next Steps

• What other KPIs or visualizations would you add to make this dashboard more useful?

• What questions does the dashboard raise that aren't answered?

• If you had access to the data, what deeper analysis would you perform?

Dashboard  #2

Supply chain Dashboard

Repeat the steps for above dashboard:

1. Observe the dashboard

2. Make inferences about the data

3. Infer business questions and insights

4. Identify Potential Improvements/Next Steps

Documentation : Install Workbench

Run this SQL file in workbench to have zomato database with 1 lakh rows in place:

• SQL File

manually querying for updated data every day?

Or is there some other way?

A stored procedure is a precompiled group of SQL statements stored in the database. You can execute it by calling it by name. It helps:

• Reuse code across applications.

• Encapsulate business logic.

• Improve performance for complex or repeated queries.

Instead of manually querying for updated data every day, you can create a stored procedure:

Then in Python:

• Problem-Solving Ability: Identify patterns, troubleshoot issues, and develop solutions from complex datasets.

• Logical Thinking: Interpreting trends, correlations, and anomalies in data.

• Adaptability to Tools and Methods: Quickly learn new tools, programming languages, or analytical techniques as technology evolves.

• Decision-Making Under Uncertainty: Make informed decisions even when data is incomplete, ambiguous, or conflicting.

Solve percentage-based problems.

Apply ratio and proportion concepts to quantitative problems.

Calculate averages effectively. 

Analyze profit and loss scenarios. 

Number Series

Letter Series

Code and Decode

Syllogism

1. Q: What is a window function in SQL?
A: A function that performs calculations across rows related to the current row (e.g., ROW_NUMBER(), RANK()).

2. Q: What is the use of WITH clause (CTE) in SQL?
A: It defines a temporary result set to improve readability and reusability in complex queries.

3. Q: Difference between RANK() and DENSE_RANK()?
A: RANK() skips numbers after ties; DENSE_RANK() doesn’t.

4. Q: What is a What-If parameter in Power BI?
A: It allows users to simulate different scenarios by dynamically changing input values to see their effect on measures.

5. Q: How is DirectQuery different from Import mode?
A: Import stores data in Power BI; DirectQuery queries the source live—best for real-time data but slower performance.

6. Q: When would you use CALCULATE() in Power BI?
A: To change the context of a measure, e.g., calculating sales for a specific region:
CALCULATE(SUM(Sales[Amount]), Region="East")

7. Q: What is the use of Google Colab in data analysis?
A: It's a cloud-based notebook that supports Python—used for writing, running, and sharing code without setup.

8. Q: How is SQL Workbench used in projects?
A: For writing, testing, and executing SQL queries—commonly used in data transformation and retrieval tasks.

AI tools for analysis

• Julius 

• Perplexity Labs

• Google Colab

Julius

Let’s further our cause by using different tools for different purposes! 

1. Use same Dataset - Global Retail & Online Store Sales.xlsx

2. Open Julius & login using your email address.

3. Attach the file and ask - “Create a workflow for: Identify customer segments with high return rates.”

Perplexity Labs

Let’s use our Global Retail Sales data from our one of the previous sprints & try to identify trends directly using AI tools. 

1. Download Dataset - Global Retail & Online Store Sales.xlsx

2. Open Perplexity Labs & login using your email address.

3. Attach the file and ask - “Referencing the attached file - What were the major trends in global retail sales during 2024?”

Google Colab

1. Login to Google Colab using your personal gmail ID.

2. Click + New Notebook

3. Analyze files with Gemini

4. Use same Dataset - Global Retail & Online Store Sales.xlsx

• Compare and/or contrast - Type 1

Example: What is the difference between Data Mining and Data profiling?

• How can we do this/ How does it happen/ Why is this not possible - Type 2

Example: What are the various steps involved in any analytics project?

• What is it/ Why do we need it/ How is it used - Type 3

Example: What is the significance of Exploratory Data Analysis (EDA)?

Intro line -

(What are both of these used for / how are they similar)

3 Points compared and contrasted - 

Data Mining and Data profiling?

UseCase / Similarity:

• Both are essential steps in preparing and understanding data before it is used for decision-making or machine learning.

• Data Mining = "What can we learn from this data?"

• Data Profiling = "What does this data look like and is it usable?" 

3 points compared and contrasted:

How, if possible, otherwise why not -

2 Things to keep in mind while doing so -

(best practice/pitfalls)

Need -

(Give a real world deficit.)

What is it? & How is it used?

(fulfill the NEED section.)

Need

• Before jumping into modeling or drawing conclusions, it's crucial to understand the data you’re working with. Raw data often contains:

  • Missing values

  • Outliers or anomalies

  • Incorrect formats or inconsistent entries

  • Hidden patterns or biases

• EDA reduces risk and increases model effectiveness by laying a strong foundation.

What is it?

• Exploratory Data Analysis (EDA) is the initial process of analyzing data sets to:

  • Summarize their main characteristics

  • Discover patterns and relationships

  • Detect anomalies

  • Test hypotheses using statistical graphics and data visualization

• It combines statistics, visualization, and domain knowledge to extract insights before applying machine learning or advanced models.

How is it used?

• Exploratory Data Analysis (EDA) is the initial process of analyzing data sets to:

  • Summarize their main characteristics

  • Discover patterns and relationships

  • Detect anomalies

  • Test hypotheses using statistical graphics and data visualization

• It combines statistics, visualization, and domain knowledge to extract insights before applying machine learning or advanced models.

Data visualization has grown rapidly in popularity due to its ease of viewing and understanding complex data in the form of charts and graphs. In addition to providing data in a format that is easier to understand, it highlights trends and outliers. The best visualizations illuminate meaningful information while removing noise from data.

In the field of Time Series Analysis (TSA), a sequence of data points is analyzed over an interval of time. Instead of just recording the data points intermittently or randomly, analysts record data points at regular intervals over a period of time in the TSA. It can be done in two different ways: in the frequency and time domains. As TSA has a broad scope of application, it can be used in a variety of fields. TSA plays a vital role in the following places: 

• Statistics 

• Signal processing 

• Econometrics 

• Weather forecasting 

• Earthquake prediction 

• Astronomy 

• Applied science