Jawad Jamshaid

Project Description

We will work with superstore data in PostgreSQL, reformatting and analyzing it to answer some day-to-day retail business questions, like what are the top-performing products and how missing data can be imputed for the quantity of products per order.

Cleaning a PostgreSQL Database

In this project, we will work with data from a hypothetical Super Store to challenge and enhance our SQL skills in data cleaning. This project will engage us in identifying top categories based on the highest profit margins and detecting missing values, utilizing your comprehensive knowledge of SQL concepts.

Data Dictionary:

`orders`:

`returned_orders`:

`people`:

`products`:

As we can see in the Data Dictionary above, date fields have been written to the `orders` table as `TEXT` and numeric fields like sales, profit, etc, have been written to the `orders` table as `Double Precision`. We will need to take care of these types in some of the queries.

Let's begin!.

Find the top 5 products from each category based on highest total sales.

Calculate the quantity for orders with missing values in the quantity column by determining the unit price for each product_id using available order data, considering relevant pricing factors such as discount, market, or region. Then, use this unit price to estimate the missing quantity values.

Project Description

As electric vehicles (EVs) become more popular, there is an increasing need for access to charging stations, also known as ports. To that end, many modern apartment buildings have begun retrofitting their parking garages to include shared charging stations. A charging station is shared if it is accessible to anyone in the building.

But with increasing demand comes competition for these ports — nothing is more frustrating than coming home to find no charging stations available! In this project, we will use a dataset to help apartment building managers better understand their tenants’ EV charging habits.

The data has been loaded into a PostgreSQL database with a table named charging_sessions with the following columns:

charging_sessions

Let’s get started!

Sources

- Data: CC BY 4.0, via Kaggle,

Find the number of unique individuals that use each garage’s shared charging stations.

Find the top 10 most popular charging start times (by weekday and start hour) for sessions that use shared charging stations.

Find the users whose average charging duration last longer than 10 hours when using shared charging stations.

Project Description:

When factoring heat generation required for the manufacturing and transportation of products, Greenhouse gas emissions attributable to products, from food to sneakers to appliances, make up more than 75% of global emissions. (Source: The Carbon Catalogue)

Our data, which is publicly available on nature.com, contains product carbon footprints (PCFs) for various companies. PCFs are the greenhouse gas emissions attributable to a given product, measured in CO₂ (carbon dioxide equivalent).

`product_emissions`

We'll use this data to examine the carbon footprint of each industry in the dataset!

Using the product_emissions table, find the number of unique companies and their total carbon footprint PCF for each industry group, filtering for the most recent year in the database.

Project Description

In today's fast-paced and competitive educational environment, understanding the factors that influence student success is more important than ever. Just like the transport system in a bustling city like London must adapt to serve its residents, schools and educators must adapt to meet the needs of students. In this project, we will take a deep dive into a dataset containing rich details about various aspects of student life, such as hours studied, sleep patterns, attendance, and more, to uncover what truly impacts exam performance.

The dataset we'll be working with includes a wide range of factors influencing student performance. By analyzing this data, we'll be able to identify key drivers of success and provide insights that could help students, teachers, and policymakers make informed decisions. The table we'll use for this project is called student_performance and includes the following data:

Let's Begin!

Do more study hours and extracurricular activities lead to better scores? Analyze how studying more than 10 hours per week, while also participating in extracurricular activities, impacts exam performance.

Is there a sweet spot for study hours? Explore how different ranges of study hours impact exam performance by calculating the average exam score for each study range.

A teacher wants to show their students their relative rank in the class, without revealing their exam scores to each other. Use a window function to assign ranks based on exam_score, ensuring that students with the same exam score share the same rank and no ranks are skipped.

Project Description

Video games are big business: the global gaming market is projected to be worth more than $300 billion by 2027 according to Mordor Intelligence. With so much money at stake, the major game publishers are hugely incentivized to create the next big hit. But are games getting better, or has the golden age of video games already passed?

In this project, we'll analyze video game critic and user scores as well as sales data for the top 400 video games released since 1977. we'll search for a golden age of video games by identifying release years that users and critics liked best, and you'll explore the business side of gaming by looking at game sales data.

Our search will involve joining datasets and comparing results with set theory. We'll also filter, group, and order data. The database contains two tables. Each table has been limited to 400 rows for this project, but you can find the complete dataset with over 13,000 games on Kaggle.

`game_sales` table

`reviews` table

`users_avg_year_rating` table

`critics_avg_year_rating` table

Find the ten best-selling games.

Find the ten years with the highest average critic score, where at least four games were released (to ensure a good sample size).

Find the years where critics and users broadly agreed that the games released were highly rated. Specifically, return the years where the average critic score was over 9 OR the average user score was over 9.

Project Description

An essential part of business is planning for the future and ensuring the business survives changing market conditions. In this project, we explored data from BusinessFinancing.co.uk on the world's oldest businesses. We'll use joining and data manipulation techniques to answer questions about these historic businesses and better understand the world's oldest businesses.

Staffelter Hof Winery is Germany's oldest business, established in 862 under the Carolingian dynasty. It has continued to serve customers through dramatic changes in Europe, such as the Holy Roman Empire, the Ottoman Empire, and both world wars. What characteristics enable a business to stand the test of time?

To help answer this question, BusinessFinancing.co.uk researched the oldest company still in business in almost every country and compiled the results into several CSV files. This dataset is already cleaned. Having useful information in different files is a common problem. While it's better to keep different types of data separate for data storage, but we want all the data in one place for analysis.

The Data

businesses and new_businesses

countries

categories

What is the oldest business on each continent?

How many countries per continent lack data on the oldest businesses? Does including `new_businesses` change this? Count the number of countries per continent missing business data, including `new_businesses`.

Which business categories are best suited to last many years, and on what continent are they?

Project Description

Since 2010, GoodThought NGO has led transformative efforts in education, healthcare, and sustainability worldwide. We will dive into a PostgreSQL database to analyze key metrics from 2010 to 2023, track donations, and assess program effectiveness. This project offers a deep dive into data, revealing the impact and outcomes of GoodThought's initiatives.

This project offers a hands-on opportunity to explore how data-driven insights can direct and enhance these humanitarian efforts. In this project, we'll engage with the detailed records of assignments, funding, impacts, and donor activities from 2010 to 2023. This comprehensive dataset includes:

• Assignments: Details about each project, including its name, duration (start and end dates), budget, geographical region, and the impact score.
• Donations: Records of financial contributions, linked to specific donors and assignments, highlighting how financial support is allocated and utilized.
• Donors: Information on individuals and organizations that fund GoodThought’s projects, including donor types.

We will execute SQL queries to answer two questions. Let's begin!

List the top five assignments based on total value of donations, categorized by donor type.

Identify the assignment with the highest impact score in each region, ensuring that each listed assignment has received at least one donation.

Project Description

Humans not only take debts to manage necessities. A country may also take debt to manage its economy. For example, infrastructure spending is one costly ingredient required for a country's citizens to lead comfortable lives. The World Bank is the organization that provides debt to countries.

In this project, you are going to analyze international debt data collected by The World Bank. The dataset contains information about the amount of debt (in USD) owed by developing countries across several categories. Let's use our data manipulation skills to find out which countries have the highest and lowest amounts of debt.

`international_debt` table

We will try find the answers to the following questions:

• What is the number of distinct countries present in the database?
• What country has the highest amount of debt?
• What country has the lowest amount of principal repayments (indicated by the "DT.AMT.DLXF.CD" indicator code)?

What is the number of distinct countries present in the database?

What country has the highest amount of debt?

What country has the lowest amount of principal repayments (indicated by the "DT.AMT.DLXF.CD" indicator code)?

Project Description

What makes a name timeless or trendy? This project uses over a century of U.S. Social Security data to explore American baby name preferences.

The data manipulation skills we'll use in this project are broadly applicable: understanding changing tastes is a key competency for businesses as well as parents searching for a baby name!

How have American baby name tastes changed since 1920? Which names have remained popular for over 100 years, and how do those names compare to more recent top baby names? These are considerations for many new parents, but the skills we'll practice while answering these queries are broadly applicable. After all, understanding trends and popularity is important for many businesses, too!

We'll be working with data provided by the United States Social Security Administration, which lists first names along with the number and sex of babies they were given to in each year. For processing speed purposes, the dataset is limited to first names which were given to over 5,000 American babies in a given year. The data spans 101 years, from 1920 through 2020.

The Data

baby_names

List the first five names in alphabetical order and find out if each name is "Classic" or "Trendy."

What were the top 20 male names overall, and how did the name Paul rank?

Which female names appeared in both 1920 and 2020?

Project Description

Let's assume to be working for a company that sells motorcycle parts, we'll dig into their data to help them understand their revenue streams. We'll build up a query to find out how much net revenue they are generating across their product lines, segregating by date and warehouse.

The company operates three warehouses in the area, selling both retail and wholesale. They offer a variety of parts and accept credit cards, cash, and bank transfers as payment methods. However, each payment type incurs a different fee.

The board of directors wants to gain a better understanding of wholesale revenue by product line, and how this varies month-to-month and across warehouses. We have been tasked with calculating net revenue for each product line and grouping results by month and warehouse. The results should be filtered so that only "Wholesale" orders are included.

We have been provided with access to their table called sales:

Sales

Find out how much Wholesale net revenue each product_line generated per month per warehouse in the dataset.

Project Description

Studying abroad can be exciting but it can be challenging too. But what might be contributing to this? One Japanese international university decided to find out! We will use data manipulation skills to explore the data from a study on the mental health of international students, and find out which factors may have the greatest impact.

Does going to university in a different country affect your mental health? A Japanese international university surveyed its students in 2018 and published a study the following year that was approved by several ethical and regulatory boards. The study found that international students have a higher risk of mental health difficulties than the general population and that social connectedness (belonging to a social group) and acculturative stress (stress associated with joining a new culture) are predictive of depression.

Let's explore the students data using PostgreSQL to find out if we would come to a similar conclusion for international students and see if the length of stay is a contributing factor.

Here is a data description of the columns that may be helpful.

Explore and analyze the students data to see how the length of stay (stay) impacts the average mental health diagnostic scores of the international students present in the study.

Project Description

Manufacturing processes vary, and ensuring consistent quality requires careful monitoring and adjustment. Sometimes, the adjustments can be too extreme and may have the opposite effect, reducing quality. We will apply SQL window functions, partitioning, and summary statistics to manufacturing data to identify if any adjustments were wrong for the company. It is like putting together a puzzle. Products are pieced together step by step, and keeping a close eye on the process is important.

For this project, let's assume that we're supporting a team that wants to improve how they monitor and control a manufacturing process. The goal is to implement a more methodical approach known as statistical process control (SPC). SPC is an established strategy that uses data to determine whether the process works well. Processes are only adjusted if measurements fall outside of an acceptable range.

This acceptable range is defined by an upper control limit (UCL) and a lower control limit (LCL), the formulas for which are:

\(ucl = avg_height + 3 * \frac{stddev_height}{\sqrt{5}}\)

\(lcl = avg_height - 3 * \frac{stddev_height}{\sqrt{5}}\)

The UCL defines the highest acceptable height for the parts, while the LCL defines the lowest acceptable height for the parts. Ideally, parts should fall between the two limits.

Using SQL window functions and nested queries, we'll analyze historical manufacturing data to define this acceptable range and identify any points in the process that fall outside of the range and therefore require adjustments. This will ensure a smooth running manufacturing process consistently making high-quality products.

The data

The data is available in the manufacturing_parts table which has the following fields:

Analyze the manufacturing_parts table and determine whether the manufacturing process is performing within acceptable control limits. Create an alert that flags whether the height of a product is within the control limits for each `operator`.

Project Description

Which industries are booming? Where should your next investment be? With the rise of the ‘amateur investor’ and the availability of platforms enabling people to invest and sell stock in public companies, it’s never been a better time to understand the financial performance of companies across the globe!

Using a PostgreSQL database, we will analyze information about unicorn companies worth over $1 billion. You’ll find out which industries have the highest average valuation and then zoom in on these to determine how many new unicorns have been produced annually between 2019 and 2021.

Moreover, did you know that the average return from investing in stocks is 10% per year (not accounting for inflation)? But who wants to be average?!

Let's assume supporting an investment firm by analyzing trends in high-growth companies. They are interested in understanding which industries are producing the highest valuations and the rate at which new high-value companies are emerging. Providing them with this information gives them a competitive insight as to industry trends and how they should structure their portfolio looking forward.

In the accessed database, we have the following tables:

dates

funding

industries

companies

Identify the three best-performing industries based on the number of new unicorns created in 2019, 2020, and 2021 combined.

Project Description

In this project, we worked with a made-up consulting company called HealthStat. Their goal was to find ways to improve how hospitals work. We used data from hospitals across one state and analyzed it using Power BI. Then, we created a clear and easy-to-understand dashboard to show useful insights and help HealthStat make better decisions.

Data Model

Conditional_Column

Project Description:

In this project, we used Power BI to work with a real-world dataset from a sample telecom company called Databel. The main goal was to understand and reduce customer churn (when customers stop using a service). We didn’t just find the churn rate but also looked at the reasons behind it and searched for ways to keep more customers. We created calculated columns and measures to help with our analysis and designed attractive report pages to show our findings clearly.

Project Description:

In healthcare, it’s important to improve efficiency while still giving high-quality care to patients. In this Power BI case study, we worked with a real-world dataset to find insights about hospital efficiency for a fictional consulting company called HealthStat. We analyzed the factors that affect the length of stay (LOS) and treatment costs, and looked for reasons why some hospitals perform differently than others. We used our DAX skills to create useful measures and visualizations. At the end, we put everything together into a professional dashboard to clearly share our insights with the HealthStat team. This project helped me practice many Power BI skills using real data.

Data Model:

Project Description:

In this project, we built a strong make vs. buy analysis tool using Power BI. We created a quote analysis tool, calculated costs for different production volumes, and used internal manufacturing cost data for deeper insights. This case study showed how Power BI is better than traditional spreadsheets by giving us more flexible and detailed analysis.

We also improved the model by adding extra cost factors and supply chain details. By the end of this project, we had a solid understanding of how to use Power BI for supply chain analytics and learned how to build useful tools for the organization.

Data Model

Project Description:

In this Power BI case study, we worked with a real-world job posting dataset for a fictional recruitment company called DataSearch. Our goal was to uncover trends and insights in the job market for roles like data scientists, data analysts, and data engineers.

We started by using Power Query to clean and prepare the data. We carefully explored the dataset to find out which technical and analytical skills were most in demand for each job role. Then, we used DAX to create measures and build meaningful visualizations that clearly showed our findings.

Finally, we combined all of our work into a well-designed Power BI dashboard. This dashboard helped answer key business questions and gave the DataSearch team a clear view of what employers are looking for in data professionals. This project allowed us to apply everything we had learned and showed how Power BI can help in real-world recruitment and job market analysis.

Data Model

Project Description:

In this Power BI case study, we challenged ourselves by working on a real-world-style inventory analysis project for a fictional retail company called WarmeHands Inc. Like many retail businesses, they wanted to improve how they manage inventory and make purchasing decisions — and that’s where we stepped in.

We began by using Power Query to clean and explore the dataset, making sure everything was correct and consistent. Just like in real life, the data wasn’t perfect, so we had to be careful during the cleaning process. After that, we used DAX to create useful calculations and perform deeper analysis. This helped us find key insights about inventory trends, performance, and opportunities for improvement.

Finally, we designed visually appealing and informative report pages to clearly present our findings. By the end of the project, we had a better understanding of how to carry out inventory analysis using Power BI and felt more confident applying my skills to real business challenges.

Data Model

Project Description:

In this Power BI case study, we analyzed a real-world e-commerce dataset for a fictional online pet supply company called Whiskique. Our goal was to find useful insights about the company’s sales performance and customer locations to help the executive team make better business decisions.

We started by using Power Query to clean and explore the raw data. This helped us make sure the information was accurate and ready for analysis. Then, we used DAX to create important measures and build clear visualizations that showed how the company’s sales were doing across different regions and time periods.

In the final stage, we combined everything into interactive dashboard-style report pages. These dashboards made it easy for the Whiskique leadership team to understand the data and take action. This project helped me practice real-world Power BI skills and understand how data can support better decisions in e-commerce.

Data Model

Project Description:

In this Power BI case study, we took on the role of a trader working at a mortgage lending firm. It was a fast-paced and exciting project that gave us a deeper look into how the financial system works — from large investment banks to lenders and finally to individual borrowers.

Our task was to analyze a group of mortgages, evaluate bids from potential buyers, and make decisions on which mortgages to trade. This helped us understand how money moves through the financial system and what factors influence trading decisions in the mortgage industry.

We used data modeling to structure our dataset properly, applied DAX formulas to perform financial calculations, and created clear visualizations to support our trading choices. By the end of the project, we improved our skills in Power BI, financial analysis, and gained valuable insight into the world of banking and mortgage trading.

Data Model

Project Description:

In this Power BI case study, we worked on a real-world business scenario where we applied key concepts of ETL (Extract, Transform, Load) and data visualization. Using Power BI and Power Query Editor, we built a complete report from scratch — starting with data integration from multiple sources, followed by data cleaning and transformation, data modeling, and finally creating visual dashboards.

Our main objective was to perform a detailed sales and market share analysis. We looked at the company’s internal sales performance to understand how well the products were selling. Then we went further and compared the company’s performance with other manufacturers to see how it stood in the market.

By the end of this project, we gained hands-on experience with competitive sales analysis and felt more confident using Power BI to analyze real-world datasets. This case study showed us how data can help businesses make smarter, more informed decisions.

Data Model

With the help of Internet of Things (IoT) devices, we can design data-driven models to detect problems (anomalies) in industrial machines. This is an important first step toward building smart maintenance systems in industries. It is very important to know if a machine is going to fail and what kind of failure it might have. To do this, sensors can be installed to monitor the condition of machines and collect useful data. This can help industries save a lot of money and time.

We suggest a complete solution that uses low-cost and easily available IoT sensors. These sensors will collect custom data from diesel generators. This data will be used as input for an ensemble machine learning (ML) model. The IoT-based system can detect problems in real time in different parts of the diesel generators by running the ML model on edge devices. It will also allow remote monitoring and control.

The success of this idea can be tested by running experiments on different important machine parts where sensor data is available. (Project leader from Sep 2024 to June 2025)

The method uses deep learning to analyze biomedical images and ensemble classifiers to improve classification accuracy. Its goal is to automate the diagnosis of breast cancer. The smart breast cancer prediction system acts as an early detection tool for the first signs of cancer, providing a low-cost and easy-to-use solution. ( Project member from Jan 2025 - June 2025)

Developing a mobile app to help local and international tourists. The app will use AI to give smart suggestions, recommendations, help with travel plans, and guide users during their trip. It will make it easier for tourists to find places to visit, plan their journey, and learn about different areas. The goal is to make traveling in Pakistan easier, safer, and more enjoyable.