SQL Case Study Customer Insights

 

                                                                CUSTOMER INSIGHTS


--SQL Challenge - 3 -Customer Insights

create database Customer_Insights;

CREATE TABLE country (
country_id INT PRIMARY KEY,
country_name VARCHAR(50),
head_office VARCHAR(50)
);
--------------------
INSERT INTO country (country_id, country_name, head_office)
VALUES (1, 'UK', 'London'),
(2, 'USA', 'New York'),
(3, 'China', 'Beijing');
--------------------
CREATE TABLE customers (
customer_id INT PRIMARY KEY,
first_shop DATE,
age INT,
rewards VARCHAR(50),
can_email VARCHAR(50)
);
--------------------
INSERT INTO customers (customer_id, first_shop, age, rewards, can_email)
VALUES (1, '2022-03-20', 23, 'yes', 'no'),
(2, '2022-03-25', 26, 'no', 'no'),
(3, '2022-04-06', 32, 'no', 'no'),
(4, '2022-04-13', 25, 'yes', 'yes'),
(5, '2022-04-22', 49, 'yes', 'yes'),
(6, '2022-06-18', 28, 'yes', 'no'),
(7, '2022-06-30', 36, 'no', 'no'),
(8, '2022-07-04', 37, 'yes', 'yes');
--------------------
CREATE TABLE orders (
order_id INT PRIMARY KEY,
customer_id INT,
date_shop DATE,
sales_channel VARCHAR(50),
country_id INT,
FOREIGN KEY (customer_id) REFERENCES customers(customer_id),
FOREIGN KEY (country_id) REFERENCES country(country_id)
);
--------------------
INSERT INTO orders (order_id, customer_id, date_shop, sales_channel, country_id)
VALUES (1, 1, '2023-01-16', 'retail', 1),
(2, 4, '2023-01-20', 'retail', 1),
(3, 2, '2023-01-25', 'retail', 2),
(4, 3, '2023-01-25', 'online', 1),
(5, 1, '2023-01-28', 'retail', 3),
(6, 5, '2023-02-02', 'online', 1),
(7, 6, '2023-02-05', 'retail', 1),
(8, 3, '2023-02-11', 'online', 3);
--------------------
CREATE TABLE products (
product_id INT PRIMARY KEY,
category VARCHAR(50),
price NUMERIC(5,2)
);
--------------------
INSERT INTO products (product_id, category, price)
VALUES (1, 'food', 5.99),
(2, 'sports', 12.49),
(3, 'vitamins', 6.99),
(4, 'food', 0.89),
(5, 'vitamins', 15.99);
--------------------
CREATE TABLE baskets (
order_id INT,
product_id INT,
FOREIGN KEY (order_id) REFERENCES orders(order_id),
FOREIGN KEY (product_id) REFERENCES products(product_id)
);
--------------------
INSERT INTO baskets (order_id, product_id)
VALUES (1, 1),
(1, 2),
(1, 5),
(2, 4),
(3, 3),
(4, 2),
(4, 1),
(5, 3),
(5, 5),
(6, 4),
(6, 3),
(6, 1),
(7, 2),
(7, 1),
(8, 3),
(8, 3);


select * from baskets

select * from country

select * from customers

select * from orders

select * from products

--- Questions - Make sure to tag @Steel Data and @Matthew Steel

1. What are the names of all the countries in the country table?

select country_name 
from country;

2. What is the total number of customers in the customers table?

select count(*) as total_no_of_customers
from customers;

SELECT COUNT(CUSTOMER_ID) AS TOTAL_CUSTOMERS
FROM CUSTOMERS;

3. What is the average age of customers who can receive marketing emails (can_email is set to 'yes')?

select round(avg(age),0) as Avg_age
from customers
where can_email='yes';

SELECT AVG(AGE) AS AVG_AGE
FROM CUSTOMERS
WHERE can_email='YES';

4. How many orders were made by customers aged 30 or older?

select count(order_id) as Total_order
from orders
where customer_id IN
(select customer_id FROM customers WHERE AGE >=30)

------/////

SELECT COUNT(O.ORDER_ID) AS TOTAL_ORDER
FROM ORDERS O
INNER JOIN customers C
ON O.customer_id = C.customer_id
WHERE AGE>=30;


5. What is the total revenue generated by each product category?

SELECT P.CATEGORY,SUM(P.PRICE) AS T0T_REVENUE_BY_PRODUCT
FROM PRODUCTS P INNER JOIN BASKETS B
ON P.product_id = B.product_id
INNER JOIN ORDERS O
ON O.order_id = B.order_id
GROUP BY P.category
ORDER BY T0T_REVENUE_BY_PRODUCT DESC;

SELECT CATEGORY,
SUM(PRICE) AS TOTAL_REVENUE
FROM PRODUCTS P JOIN BASKETS B
ON P.product_id = B.product_id
GROUP BY category
ORDER BY TOTAL_REVENUE DESC;

6. What is the average price of products in the 'food' category?

SELECT AVG(PRICE) AS AVG_PRICE
FROM PRODUCTS
WHERE category = 'FOOD';

SELECT ROUND(AVG(PRICE),2) AS AVG_PRICE
FROM PRODUCTS
WHERE category = 'FOOD'
GROUP BY category;

7. How many orders were made in each sales channel (sales_channel column) in the orders table?

SELECT SALES_CHANNEL,COUNT(ORDER_ID) AS TOT_ORDERS
FROM ORDERS 
GROUP BY sales_channel
ORDER BY TOT_ORDERS;

8.What is the date of the latest order made by a customer who can receive marketing emails?

SELECT MAX(O.DATE_SHOP) AS LATERST_ORDER_DATE
FROM ORDERS AS O JOIN CUSTOMERS AS C
ON O.customer_id = C.customer_id
WHERE can_email='YES';


9. What is the name of the country with the highest number of orders?

SELECT TOP 1 C.COUNTRY_NAME
FROM COUNTRY AS C JOIN ORDERS AS O
ON C.country_id = O.country_id
GROUP BY country_name
ORDER BY COUNT(ORDER_ID) DESC;

10. What is the average age of customers who made orders in the 'vitamins' product category?


select category,round(avg(age),3) as avg_age
from customers as c 
join orders as o
on o.customer_id = c.customer_id
join baskets as b
on o.order_id = b.order_id
join products as p
on p.product_id= b.order_id
where category = 'vitamins'
group by category;


--------

Follow @Thanaselvi C for more updates :

Linked in : https://www.linkedin.com/in/thanaselvi-c-98310211a/

Comments

Post a Comment

Popular posts from this blog

SQL Case Study Finance Analysis

Image
                                                                    Finance Analysis SQL Challenge 4 - Challenge 4 - Finance Analysis   create database Finance_Analysis -- Create the Customers table CREATE TABLE Customers ( CustomerID INT PRIMARY KEY, FirstName VARCHAR(50) NOT NULL, LastName VARCHAR(50) NOT NULL, City VARCHAR(50) NOT NULL, State VARCHAR(2) NOT NULL ); -------------------- -- Populate the Customers table INSERT INTO Customers (CustomerID, FirstName, LastName, City, State) VALUES (1, 'John', 'Doe', 'New York', 'NY'), (2, 'Jane', 'Doe', 'New York', 'NY'), (3, 'Bob', 'Smith', 'San Francisco', 'CA'), (4, 'Alice', 'Johnson', 'San Francisco', 'CA'), (5, 'Michael', 'Lee', 'Los Angeles', 'CA'), (6, 'Jennifer', 'Wang', 'Los Angel...
Read more

Danny's Dinner

Image
                                                                                       Danny's Dinner SQL Challenge - 1 Data set link : https://8weeksqlchallenge.com/getting-started/ 3 key datasets for this case study: Entity Relationship Diagram Case Study Questions Each of the following case study questions can be answered using a single SQL statement: create database Dannys_Dinner CREATE TABLE sales (   "customer_id" VARCHAR(1),   "order_date" DATE,   "product_id" INTEGER ); INSERT INTO sales   ("customer_id", "order_date", "product_id") VALUES   ('A', '2021-01-01', '1'),   ('A', '2021-01-01', '2'),   ('A', '2021-01-07', '2'),   ('A', '2021-01-10', '3'),   ('A', '2021-01-11', '3'), ...
Read more

A Day in the Life of a Data Analyst Interviewee

                                                            Data Analyst⌛ Am sharing my interview experience held on  Nov:4:2023✅ As usual : Tell me about your previous project work experience and tools used. ☝SQL : Joins - Left join ,Right join, Inner Join, Outer join Like operators - Like 'A%; What is a Stored Procedure?  A stored procedure is a prepared SQL code that you can save, so the code can be reused over and over again. So if you have an SQL query that you write over and over again, save it as a stored procedure, and then just call it to execute it. Different Types of Indexes in SQL Server  There are various types of indexes in SQL server:  Clustered Index  Non-Clustered Index  Column Store Index  Filtered Index  Hash Index  Unique Index  ☺Power BI - ...
Read more