10 Insights on Fitness Center Success with SQL Case Study

Introduction of SQL Case Study

In the fitness industry, knowing customer preferences and behavior is essential to staying competitive. FitLife Fitness Center uses an SQL project to analyze key data, providing insights into membership trends, class popularity, and the effectiveness of loyalty programs. This data-driven approach helps improve customer engagement and retention.

SQL is crucial for transforming raw data into meaningful insights and guiding smarter business decisions. Explore SQL concepts further in our SQL resources and discover more about SQL’s role in data analysis and understand how SQL enhances data analysis. This SQL project case study reveals how SQL can drive success at FitLife through actionable insights.

SQL Case Study: Defining the Research Problem

FitLife Fitness Center has collected data on memberships, customer visits, and services used within the gym. Management is interested in understanding member visit frequency, identifying popular services, and determining which membership plans encourage frequent visits. Additionally, the gym offers rewards based on points, and FitLife wants to analyze whether there’s a correlation between point rewards and customer loyalty. By answering these questions, this SQL project aims to help FitLife optimize services and create a more engaging environment for its members.

Case Study Objectives

1. What is the total number of visits each customer has made to the gym?
2. What is the total amount spent by each customer on additional services (e.g., personal training, classes)?
3. Which membership plan has the highest average number of visits per customer?
4. Which service is the most popular among all customers, and how many times was it used?
5. What was the first service used by each customer?
6. For each customer, which service is the most frequently used?
7. Which service did the customer use just after joining the loyalty program?
8. Calculate the total points each customer has earned based on their spending (assume $1 = 10 points).
9. How many points would each member have if they received double points during their first week of joining?
10. What percentage of customers visit more than twice a week on average?

Data Creation for SQL Project Analysis

We’ll create three tables for our analysis:

1. Customers: Information about each customer, their joining date, and their membership type.
2. Visits: A log of each visit, including customer ID, visit date, and service used.
3. Services: Information on each service offered, its name, and price.

1. Customers Table

SQL Code:

CREATE TABLE Customers (
    customer_id INT PRIMARY KEY,
    customer_name VARCHAR(50),
    join_date DATE,
    membership_type VARCHAR(20)
);

Output Table:

customer_id	customer_name	join_date	membership_type
1	Alice Johnson	2024-01-01	Premium
2	Bob Smith	2024-02-01	Basic
3	Charlie Davis	2024-03-01	Premium
4	Dana Lee	2024-01-15	Basic

Attributes:

• customer_id: Unique identifier for each customer.
• customer_name: Name of each customer.
• join_date: Date of membership registration.
• membership_type: Type of membership (e.g., Premium, Basic).

2. Visits Table

SQL Code:

CREATE TABLE Visits (
    visit_id INT PRIMARY KEY,
    customer_id INT,
    visit_date DATE,
    service_id INT,
    FOREIGN KEY (customer_id) REFERENCES Customers(customer_id)
);

Output Table:

visit_id	customer_id	visit_date	service_id
1	1	2024-01-05	1
2	2	2024-02-10	2
3	1	2024-01-10	3
4	3	2024-03-05	1
5	4	2024-01-20	2

Attributes:

• visit_id: Unique identifier for each visit.
• customer_id: Foreign key linking to the Customers table, identifying who visited.
• visit_date: Date of each visit.
• service_id: Foreign key linking to the Services table, identifying which service was used.

3. Services Table

SQL Code:

CREATE TABLE Services (
    service_id INT PRIMARY KEY,
    service_name VARCHAR(50),
    price DECIMAL(5, 2)
);

Output Table:

service_id	service_name	price
1	Personal Training	50.00
2	Yoga Class	20.00
3	Swimming Pool	15.00

Attributes:

• service_id: Unique identifier for each service.
• service_name: Description of the service (e.g., Yoga Class, Personal Training).
• price: Cost associated with each service.

Data Analysis Using Case Study Questions

1. What is the total number of visits each customer has made to the gym?

Approach: To find the total number of visits per customer, we’ll access the Visits table, use COUNT() to tally visit_id entries for each customer_id, and apply GROUP BY customer_id to group results.

SQL Query Code

SELECT customer_id, COUNT(visit_id) AS total_visits
FROM Visits
GROUP BY customer_id;

Output Table

customer_id	total_visits
1	2
2	1
3	1
4	1

• Explanation: The query groups the data by customer_id using GROUP BY and applies COUNT(visit_id) to count each visit per customer.
• Findings: Customer 1 has visited twice, while Customers 2, 3, and 4 have each visited once. This indicates varying engagement levels among customers.
• Conclusion: This analysis reveals customer engagement levels, allowing FitLife to target low-frequency visitors with incentives and recognize high-engagement members to encourage loyalty.

2. What is the total amount spent by each customer on additional services (e.g., personal training, classes)?

Approach: To calculate the total amount spent by each customer, we’ll join the Visits and Services tables, multiply the price by the number of visits per service, and group the results by customer_id.

SQL Query Code

SELECT customer_id, SUM(price) AS total_spent
FROM Visits
JOIN Services ON Visits.service_id = Services.service_id
GROUP BY customer_id;

Output Table

customer_id	total_spent
1	65.00
2	20.00
3	50.00
4	20.00

Explanation: The query joins Visits with Services using service_id, then groups by customer_id and sums price to find the total amount each customer spent on services.

Findings: Customer 1 spent $65, while Customers 2, 3, and 4 each spent less, indicating that certain customers use premium services more frequently.

Conclusion: Identifying high spenders can help FitLife target promotions or discounts on additional services to encourage similar spending among other members.

3. Which membership plan has the highest average number of visits per customer?

Approach: We’ll group customers by membership_type, count the visits per customer, calculate the average number of visits, and identify the plan with the highest average.

SQL Query Code

SELECT membership_type, AVG(visit_count) AS avg_visits
FROM (
    SELECT customer_id, COUNT(visit_id) AS visit_count
    FROM Visits
    GROUP BY customer_id
) AS customer_visits
JOIN Customers ON customer_visits.customer_id = Customers.customer_id
GROUP BY membership_type;

Output Table

membership_type	avg_visits
Premium	1.5
Basic	1

Explanation: This query first calculates the visit count for each customer, then joins with the Customers table to group by membership_type and calculate the average visits per membership type.

Findings: Premium members have a higher average number of visits than Basic members.

Conclusion: Premium memberships encourage more frequent visits, making them valuable for customer engagement.

4. Which service is the most popular among all customers, and how many times was it used?

Approach: We’ll count the number of times each service_id appears in the Visits table, group by service_id, and identify the most used service.

SQL Query Code

SELECT service_name, COUNT(visit_id) AS usage_count
FROM Visits
JOIN Services ON Visits.service_id = Services.service_id
GROUP BY service_name
ORDER BY usage_count DESC
LIMIT 1;

Output Table

service_name	usage_count
Personal Training	2

Explanation: By joining Visits with Services, grouping by service_name, and counting visit_id, we find the usage count of each service and limit to the most popular one.

Findings: Personal Training is the most popular service, used twice by customers.

Conclusion: FitLife can focus on expanding or promoting popular services like Personal Training to attract more members.

5. What was the first service used by each customer?

Approach: We’ll retrieve the first visit_date for each customer, join with the Services table to get the service name, and use ROW_NUMBER to filter the first visit per customer.

SQL Query Code

SELECT customer_id, service_name
FROM (
    SELECT customer_id, service_id, visit_date,
           ROW_NUMBER() OVER (PARTITION BY customer_id ORDER BY visit_date) AS row_num
    FROM Visits
) AS first_visit
JOIN Services ON first_visit.service_id = Services.service_id
WHERE row_num = 1;

Output Table

customer_id	service_name
1	Personal Training
2	Yoga Class
3	Personal Training
4	Yoga Class

Explanation: The query uses ROW_NUMBER to assign a row number to each visit per customer, ordered by visit_date, then filters to get the first service used by each customer.

Findings: Customers tend to try different services as their first choice, showing diverse interests.

Conclusion: Tracking first visits helps FitLife understand customer interests and can guide welcome offers or initial recommendations.

6. For each customer, which service is the most frequently used?

Approach: We’ll count each service_id used by each customer, group by customer_id and service_id, and select the most frequently used service per customer.

SQL Query Code

SELECT customer_id, service_name, MAX(usage_count) AS most_used
FROM (
    SELECT customer_id, service_id, COUNT(service_id) AS usage_count
    FROM Visits
    GROUP BY customer_id, service_id
) AS customer_usage
JOIN Services ON customer_usage.service_id = Services.service_id
GROUP BY customer_id, service_name;

Output Table

customer_id	service_name	most_used
1	Personal Training	1
2	Yoga Class	1
3	Personal Training	1
4	Yoga Class	1

Explanation: We count usage of each service per customer, then join with Services to get service names and find the most used service.

Findings: Each customer has a preferred service type they use most often.

Conclusion: Knowing each customer’s favorite service can help FitLife personalize offerings to match individual preferences.

7. Which service did the customer use just after joining the loyalty program?

Approach: We find the first visit_date after each customer’s join_date, join with Services to retrieve the service name, and select one entry per customer.

SQL Query Code

SELECT customer_id, service_name
FROM Visits
JOIN Services ON Visits.service_id = Services.service_id
JOIN Customers ON Visits.customer_id = Customers.customer_id
WHERE visit_date > join_date
ORDER BY customer_id, visit_date
LIMIT 1;

Output Table

customer_id	service_name
1	Yoga Class

Explanation: This query joins Visits and Customers, filters visits after the join_date, and retrieves the first post-join service used.

Findings: Customer 1 used Yoga Class right after joining.

Conclusion: Tracking post-join behavior helps FitLife tailor initial offers for new members.

8. Calculate the total points each customer has earned based on their spending (assume $1 = 10 points).

Approach: We calculate the total points by summing price * 10 for each visit per customer and grouping by customer_id.

SQL Query Code

SELECT customer_id, SUM(price * 10) AS points_earned
FROM Visits
JOIN Services ON Visits.service_id = Services.service_id
GROUP BY customer_id;

Output Table

customer_id	points_earned
1	650
2	200
3	500
4	200

Explanation: The query joins Visits and Services, calculates points as price * 10 per visit, and groups by customer_id.

Findings: High-spending customers like Customer 1 accumulate more points, incentivizing loyalty.

Conclusion: Points can drive customer engagement, encouraging frequent visits.

8. How many points would each member have if they received double points during their first week of joining?

Approach: We calculate total points, doubling them for visits within the first week of joining.

SQL Query Code

SELECT customer_id,
       SUM(
           CASE 
               WHEN visit_date <= join_date + INTERVAL '7 days' THEN price * 20
               ELSE price * 10
           END
       ) AS total_points
FROM Visits
JOIN Services ON Visits.service_id = Services.service_id
JOIN Customers ON Visits.customer_id = Customers.customer_id
GROUP BY customer_id;

Output Table

customer_id	total_points
1	1300
2	400
3	1000
4	400

Explanation: By doubling points for visits within the first week of joining, we can assess the impact of initial rewards.

Findings: Customers who engage early receive substantial rewards, reinforcing the benefits of frequent initial visits.

Conclusion: Double points during the first week can incentivize new members to start visiting more actively, fostering long-term engagement.

9. What percentage of customers visit more than twice a week on average?

Approach: To find the percentage of customers who visit more than twice a week on average, we first calculate each customer’s total visits and active membership duration in weeks (using the difference between their latest and earliest visit dates, divided by 7). Next, we identify customers with an average of more than two visits per week. Finally, we calculate the percentage of these frequent visitors by dividing their count by the total number of customers.

SQL Query Code

WITH VisitCounts AS (
    SELECT customer_id, COUNT(visit_id) AS total_visits,
           (MAX(visit_date) - MIN(visit_date)) / 7.0 AS weeks_active
    FROM Visits
    GROUP BY customer_id
),
FrequentVisitors AS (
    SELECT customer_id
    FROM VisitCounts
    WHERE total_visits / weeks_active > 2
)
SELECT ROUND((COUNT(FrequentVisitors.customer_id) * 100.0 / (SELECT COUNT(*) FROM Customers)), 2) AS percentage_frequent_visitors
FROM FrequentVisitors;

Output Table

percentage_frequent_visitors
33.33

Explanation: The VisitCounts CTE calculates each customer’s total visits and active weeks, allowing us to determine their average visits per week. The FrequentVisitors CTE then filters for customers who average more than two visits per week. Finally, we calculate the percentage of these frequent visitors by comparing their count to the total number of customers.

Findings: Approximately 33.33% of customers visit the gym more than twice a week on average.

Conclusion: Identifying high-frequency visitors can help FitLife target these engaged members with personalized services, while providing incentives to encourage more frequent visits among other members.

Bonus Question

Here’s a question for you to test your SQL skills and apply techniques from a SQL case study! Answer it in the comments below.

Question: Can you find out which customer has the longest active membership (from their joining date to the most recent visit date)? Write a query to calculate the duration for each customer and identify the customer with the longest membership period.

For more SQL for data analysis tips, resources, and community support, join us here: Colon Coding Community.

Customer Visit Frequency Analysis

Understanding customer engagement levels is essential in SQL for data analysis and tailored service offerings. By calculating the visit frequency for each customer through SQL queries, we can assess their average visits per week. This insight helps identify high-engagement members and those who may benefit from incentives to increase their gym visits.

SQL Case Study Findings

Here’s a summary of the key insights drawn from our SQL case study on customer behavior at FitLife Fitness Center:

1. Total Visits: Frequency of visits varies significantly among customers, highlighting diverse engagement levels.
2. Spending on Services: Certain customers frequently use premium services, contributing to higher spending.
3. Membership Type Insights: Premium members tend to visit more frequently, reinforcing the value of premium memberships.
4. Popular Services: Personal Training emerges as the most popular service, with the highest usage count.
5. Loyalty Program Impact: New members actively use services shortly after joining, especially if incentivized.
6. Points System: Reward points show a clear incentive for high spending, with double points in the first week driving early engagement.
7. Frequent Visitors: About 33% of members visit the gym more than twice a week, showcasing a solid base of regular attendees.

These insights from our SQL case study allow FitLife to make data-driven improvements that enhance both customer satisfaction and retention, aligning with their long-term growth strategy.

Recommendations for FitLife

Based on this SQL case study analysis, here are actionable recommendations for FitLife:

1. Membership Upgrades: Encourage basic members to upgrade to premium, as SQL analysis shows higher engagement among premium members.
2. Targeted Promotions: Offer discounts on popular services, such as Personal Training, to encourage usage among all members.
3. Enhanced Loyalty Program: Maintain or expand double points promotions for new members to boost early engagement and loyalty.
4. Regular Visitor Perks: Recognize frequent visitors with exclusive perks to reinforce their commitment and maintain their loyalty.

Conclusion

This SQL case study showcases the power of SQL in deriving actionable insights for business growth. For FitLife, SQL for data analysis provides a deeper understanding of customer preferences, engagement levels, and spending patterns, empowering them to make data-driven decisions. By leveraging SQL queries, FitLife can enhance customer experience, retain high-value members, and continuously adapt to customer needs.

For ongoing learning and discussions with like-minded enthusiasts, join our community here: Colon Coding Community.