Grocery Sales Analysis Report Query

-- MySQL Query --
1. Ascertain amount generated from each product

Select p.product_id,
       p.name,
       Sum(tr.Amount) as Total_Amount
From transaction_report tr
	Join products p
		On p.product_id=tr.product_id
Group by name -- Grouping by name helps to sum up Amount for individual products
Order by name
----------------------------------------------------------------------------------------------------------------


2. Busiest hour

Select product_id,
       quantity,
       paid_at,
       sum(Amount) as Total_Amount
From transaction_report
Group by quantity  -- Grouping by quantity helps to find the hour in which many products were bought
Order by quantity desc
Limit 1
----------------------------------------------------------------------------------------------------------------

3. Find customers that generates the least income

Select tr.customer_id,
       concat_ws(" ",first_name,last_name) as full_name,
       Sum(Amount) as Total_Amount
From loyalty_customers lc
	Join transaction_report tr  -- Here we join transaction report table to retrieve Amount and customer id
		On lc.loyalty_id=tr.customer_id
Group by full_name
Order by Total_Amount   -- This is not in descending order because we need the least to be at the top
Limit 5
-------------------------------------------------------------------------------------------------------------------

4. Find employee that spent the least days working at the grocery store

Select *,
       end_date - start_date as years_worked,
       (end_date - start_date)*365 as days_worked  -- There are 365 days in 1 year, therefore we need to multiply number of years by 365 to get number of days
From employees
Where employee_id= 7
-------------------------------------------------------------------------------------------------------------------

5. Ascertain income realize from sales

Select Sum(Amount) as Total_Sales
From transaction_report
-------------------------------------------------------------------------------------------------------------------

6. Find the day of the week which the grocery store makes the most sales

select product_id,
       quantity,
       paid_at,
       Sum(Amount) as Total_Amount
From transaction_report
Group by quantity
------------------------------------------------------------------------------------------------------------------

7. What is the Average daily sales?

select product_id,
       substr(paid_at,1,instr(paid_at," ")) as date,
       substr(paid_at,instr(paid_at," ")) as time,
       Amount,
       Avg(Amount) as Average_Sales
From transaction_report
Group by paid_at
-----------------------------------------------------------------------------------------------------------------

8. What is the best Selling products?

Select p.product_id,
       name,
       sum(Amount) as total_amount
From products p
	Join transaction_report tr
		On p.product_id=tr.product_id
	Where customer_id != 0 -- There's no loyalty customer with id=0, therefore we exclude id number 0
group by p.name
order by total_amount desc
Limit 5
----------------------------------------------------------------------------------------------------------------

9. Find the top 5 Loyalty customers

select loyalty_id,
       concat(first_name," ",last_name) as full_name,
       sum(Amount) as Total_Amount
From loyalty_customers lc
	Join transaction_report tr
		on lc.loyalty_id=tr.customer_id
Group by customer_id -- This helps to sum amount separately
Order by Total_Amount desc
limit 5
---------------------------------------------------------------------------------------------------------------

10. Which customer patronize products that generates the highest income?

Select tr.customer_id,
       concat_ws(" ",first_name,last_name) as customer_name,
       p.product_id,
       p.name,
       Sum(tr.Amount) as Total_Amount
From loyalty_customers lc
	Join transaction_report tr   -- Tables are joined here because there's a relation between loyalty customers and transaction report tables
		On lc.loyalty_id=tr.customer_id
	Join products p              -- Here we join products table to transaction report table to get the product name
		On tr.product_id=p.product_id
Group by tr.product_id
Order by Total_Amount desc
Limit 1
------------------------------------------------------------------------------------------------------------

11. Find the product that generates the least income and by much

Select p.product_id,
       p.name,
       sum(tr.Amount) as Total_Amount
From products p
	Join transaction_report tr
		On p.product_id=tr.product_id
Group by name -- Grouping by name or product_id helps to sum up the amount for each product
Order by Total_Amount
limit 1