Challenge 1 Steve's car showroom.md

Challenge 1 - Steve's Car Showroom

Intro

Steve runs a top-end car showroom, but his data analyst has just quit and left him without his crucial insights. As an interim Data analyst for Steve, I analyzed the following data to provide him with all the answers he requires.

Tables

Here are the tables you will be using:

Table Creation:

USE steel_data;
CREATE TABLE cars (
car_id INT PRIMARY KEY,
make VARCHAR(50),
type VARCHAR(50),
style VARCHAR(50),
cost_$ INT
);
--------------------
INSERT INTO cars (car_id, make, type, style, cost_$)
VALUES (1, 'Honda', 'Civic', 'Sedan', 30000),
(2, 'Toyota', 'Corolla', 'Hatchback', 25000),
(3, 'Ford', 'Explorer', 'SUV', 40000),
(4, 'Chevrolet', 'Camaro', 'Coupe', 36000),
(5, 'BMW', 'X5', 'SUV', 55000),
(6, 'Audi', 'A4', 'Sedan', 48000),
(7, 'Mercedes', 'C-Class', 'Coupe', 60000),
(8, 'Nissan', 'Altima', 'Sedan', 26000);
--------------------
CREATE TABLE salespersons (
salesman_id INT PRIMARY KEY,
name VARCHAR(50),
age INT,
city VARCHAR(50)
);
--------------------
INSERT INTO salespersons (salesman_id, name, age, city)
VALUES (1, 'John Smith', 28, 'New York'),
(2, 'Emily Wong', 35, 'San Fran'),
(3, 'Tom Lee', 42, 'Seattle'),
(4, 'Lucy Chen', 31, 'LA');
--------------------
CREATE TABLE sales2 (
sale_id INT PRIMARY KEY,
car_id INT,
salesman_id INT,
purchase_date DATE,
FOREIGN KEY (car_id) REFERENCES cars(car_id),
FOREIGN KEY (salesman_id) REFERENCES salespersons(salesman_id)
);
--------------------
INSERT INTO sales2 (sale_id, car_id, salesman_id, purchase_date)
VALUES (1, 1, 1, '2021-01-01'),
(2, 3, 3, '2021-02-03'),
(3, 2, 2, '2021-02-10'),
(4, 5, 4, '2021-03-01'),
(5, 8, 1, '2021-04-02'),
(6, 2, 1, '2021-05-05'),
(7, 4, 2, '2021-06-07'),
(8, 5, 3, '2021-07-09'),
(9, 2, 4, '2022-01-01'),
(10, 1, 3, '2022-02-03'),
(11, 8, 2, '2022-02-10'),
(12, 7, 2, '2022-03-01'),
(13, 5, 3, '2022-04-02'),
(14, 3, 1, '2022-05-05'),
(15, 5, 4, '2022-06-07'),
(16, 1, 2, '2022-07-09'),
(17, 2, 3, '2023-01-01'),
(18, 6, 3, '2023-02-03'),
(19, 7, 1, '2023-02-10'),
(20, 4, 4, '2023-03-01');

Ad-Hoc Questions and Solutions

1. What are the details of all cars purchased in the year 2022?

SELECT *
FROM cars
WHERE car_id IN (
   SELECT car_id
   FROM sales2
   WHERE YEAR(purchase_date) = 2022
);

2. What is the total number of cars sold by each salesperson?

SELECT s.name AS Salesperson,
     COUNT(*) AS Total_Cars_Sold
FROM salespersons s
JOIN sales2 sa ON s.salesman_id = sa.salesman_id
GROUP BY s.name;

3.What is the total revenue generated by each salesperson?

SELECT s.name AS Salesperson,
      SUM(c.cost_$) AS Total_Revenue_Generated
FROM salespersons s
JOIN sales2 sa ON s.salesman_id = sa.salesman_id
JOIN cars c ON sa.car_id = c.car_id
GROUP BY s.name;

4. What are the details of the cars sold by each salesperson?

SELECT s.name AS Salesperson,
       c.*
FROM salespersons s
JOIN sales2 sa ON s.salesman_id = sa.salesman_id
JOIN cars c ON sa.car_id = c.car_id;

5. What is the total revenue generated by each car type?

SELECT c.type,
       SUM(c.cost_$) AS Total_Revenue_Generated
FROM cars c
JOIN sales2 sa ON c.car_id = sa.car_id
GROUP BY c.type;

6. What are the details of the cars sold in the year 2021 by salesperson 'Emily Wong'?

SELECT c.*
FROM cars c
JOIN sales2 sa ON c.car_id = sa.car_id
JOIN salespersons s ON sa.salesman_id = s.salesman_id
WHERE YEAR(sa.purchase_date) = 2021 AND s.name = 'Emily Wong';

7. What is the total revenue generated by the sales of hatchback cars?

SELECT SUM(c.cost_$) AS Total_Revenue_Generated
FROM cars c
JOIN sales2 sa ON c.car_id = sa.car_id
WHERE c.style = 'Hatchback';

8. What is the total revenue generated by the sales of SUV cars in the year 2022?

SELECT SUM(c.cost_$) AS Total_Revenue_Generated
FROM cars c
JOIN sales2 sa ON c.car_id = sa.car_id
WHERE c.style = 'SUV' AND YEAR(sa.purchase_date) = 2022;
select * from sales2;
select * from cars;

9. What is the name and city of the salesperson who sold the most number of cars in the year 2023?

SELECT s.name AS Salesperson,
       s.city
FROM salespersons s
JOIN (
    -- Subquery calculates the rank of each salesperson based on the number of cars sold in 2023
    SELECT salesman_id,
           RANK() OVER (ORDER BY COUNT(*) DESC) AS Rnk
    FROM sales2
    WHERE YEAR(purchase_date) = 2023
    GROUP BY salesman_id
) t ON s.salesman_id = t.salesman_id
-- Select the top ranked result (salesperson with most cars sold)
WHERE Rnk = 1;

10. What is the name and age of the salesperson who generated the highest revenue in the year 2022?

SELECT name AS Salesperson, age
FROM (
    -- Subquery calculates total revenue generated by each salesperson in 2022 and assigns a rank
    SELECT s.name,
           s.age, 
           RANK() OVER (ORDER BY SUM(c.cost_$) DESC) AS rnk2
    FROM salespersons s
    JOIN sales2 ON s.salesman_id = sales2.salesman_id
    JOIN cars c ON sales2.car_id = c.car_id
    WHERE YEAR(purchase_date) = 2022
    GROUP BY s.name, s.age
) t
-- Select the top ranked result (salesperson with highest revenue)
WHERE rnk2 = 1;