Big SQL Energy Beginner Course: Lesson 29 Case Study: Calculate a Percent in SQL

Jan 29, 2025

Got BDE?⚡️

Hi BDEs! Welcome to Lesson 29 of the free Big SQL Energy Beginner Course⚡️ Today we’re doing a CASE STUDY on calculating percentages in SQL!

Return to Big SQL Energy Beginner homepage here to access the other lessons:

Return to Homepage 📚

If you’re new here:

💁🏽‍♀️ Who Am I?

I’m Jess Ramos, the founder of Big Data Energy and the creator of the BEST SQL course and community: Big SQL Energy⚡️. Check me out on socials: 🔗LinkedIn, 🔗Instagram, 🔗YouTube, and 🔗TikTok. And of course subscribe to my 🔗newsletter here for all my upcoming lessons and updates— all for free!

⚡️ What is Big SQL Energy?

🔗Big SQL Energy Intermediate is an intermediate SQL course designed to solve real-world business problems hands-on using realistic data and a modern tech stack. You’ll walk away with 2 SQL portfolio projects, portfolio building guidance, access to the Big Data Energy Discord community, and lots of confidence for you upcoming coding interviews! You also get access to all monthly events including masterclasses, office hours, and guest speakers. This course is all of the most important things I’ve learned on the job as a Senior Data Analyst in tech who grew from a $72K salary to over a $150K salary. And now I’m sharing it with YOU! It’s more than just another course— it’s a challenging program designed to upskill your SQL to the intermediate level, get ready to ace your live coding interviews, and build connections in the data community through the Discord and events.

JOIN now (pun intended 😉)

Lesson 29: Calculate a Percent Case Study

🎥 Video

We’re finally at the part where we’re using our skills to solve case studies and mini business problems— YAY! If you’re ready to learn even more skills & practice more advanced business problems to get ready for interviews and build a data analytics portfolio, you GOTTA check out my 🔗Big SQL Energy Intermediate course. I promise— it’s even better than the beginner course because it’s way more material and you get access to the community events and Discord 🔥

In SQL, we can flag conditions using CASE like this:

select 
   case when product_name = 'Laptop' then 1 else 0 end) as laptopbinary,
   case when product_name = 'Laptop' then order_id end) as laptop
from 
    orders

We can either create a column of IDs and nulls (laptop) or create a binary column of 1s and 0s (laptop_binary). Either way is fine, but I always opt for a binary column! Once we have those created, we can sum() or count() the results respectively to get a count of how many rows meet that condition we flagged. And of course, we can count(*) to get a total of all the records.

select 
    sum(case when product_name = 'Laptop' then 1 else 0 end)::int as laptop_binary,

    count(case when product_name = 'Laptop' then order_id end) as laptop,

    count(*) as total_orders
from 
    orders

Then we can create a percentage by dividing the number of orders that meet that condition by the total amount of orders:

select 
    sum(case when product_name = 'Laptop' then 1 else 0 end)::int as laptop_binary,

    count(case when product_name = 'Laptop' then order_id end) as laptop,

    count(*) as total_orders,
    laptop * 100 / total_orders as perc_laptop_binary,
    laptop * 100 / total_orders as perc_laptop
from 
    orders

Be sure to multiply the numerator by 100 if you want the percent to show up as a true percent rather than a decimal. We can repeat this process for each condition we want to flag, or in other words, each product_name. Again, you only need to use ONE method (sum a binary column like laptop_binary) OR (count a column of IDs and NULLs like laptop). You don’t need to do BOTH of these methods, but I wanted to show you both ways.

Also, we can complete this task with more advanced skills like window functions or subqueries, but for the sake of the beginner course, we’re keeping it simple and ONLY using skills we’ve learned so far. That way we can get more comfortable with this skills! But if you want to learn more advanced SQL skills, and build full projects from beginning to end, head over to my 🔗Big SQL Energy Intermediate course.

If you’ve enjoyed the free BEGINNER Big SQL Energy course, grab my intermediate course. There are no deadlines or timelines for this course— you start and take as long as you want. It will take your beginner skills to the next level and get you ready to ace your interviews. DM me or email me at courses@bdeanalytics.com if you have questions on the intermediate course!