project.sql.sqbpro

<?xml version="1.0" encoding="UTF-8"?><sqlb_project><db path="C:/Users/user/Downloads/stores.db" readonly="0" foreign_keys="1" case_sensitive_like="0" temp_store="0" wal_autocheckpoint="1000" synchronous="2"/><attached/><window><main_tabs open="structure browser pragmas query" current="3"/></window><tab_structure><column_width id="0" width="300"/><column_width id="1" width="0"/><column_width id="2" width="125"/><column_width id="3" width="4975"/><column_width id="4" width="0"/><expanded_item id="0" parent="1"/><expanded_item id="1" parent="1"/><expanded_item id="2" parent="1"/><expanded_item id="3" parent="1"/></tab_structure><tab_browse><current_table name="4,9:maincustomers"/><default_encoding codec=""/><browse_table_settings><table schema="main" name="customers" show_row_id="0" encoding="" plot_x_axis="" unlock_view_pk="_rowid_"><sort/><column_widths><column index="1" value="158"/><column index="2" value="262"/><column index="3" value="159"/><column index="4" value="161"/><column index="5" value="152"/><column index="6" value="242"/><column index="7" value="188"/><column index="8" value="145"/><column index="9" value="101"/><column index="10" value="106"/><column index="11" value="104"/><column index="12" value="237"/><column index="13" value="102"/></column_widths><filter_values/><conditional_formats/><row_id_formats/><display_formats/><hidden_columns/><plot_y_axes/><global_filter/></table><table schema="main" name="products" show_row_id="0" encoding="" plot_x_axis="" unlock_view_pk="_rowid_"><sort/><column_widths><column index="1" value="120"/><column index="2" value="300"/><column index="3" value="137"/><column index="4" value="122"/><column index="5" value="200"/><column index="6" value="300"/><column index="7" value="149"/><column index="8" value="84"/><column index="9" value="59"/></column_widths><filter_values/><conditional_formats/><row_id_formats/><display_formats/><hidden_columns/><plot_y_axes/><global_filter/></table></browse_table_settings></tab_browse><tab_sql><sql name="SQL 1">--Check data types &amp; other table information

PRAGMA table_info(customers);

--Select each table name as a string

SELECT name AS table_name
  FROM sqlite_schema
 WHERE type='table';

--Count the number of attributes per table

SELECT COUNT(*) AS number_of_attributes, 'customers' AS name
  FROM pragma_table_info('customers')
 UNION ALL
SELECT COUNT(*) AS number_of_attributes, 'employees' AS name
  FROM pragma_table_info('employees')
 UNION ALL
SELECT COUNT(*) AS number_of_attributes, 'offices' AS name
  FROM pragma_table_info('offices')
 UNION ALL
SELECT COUNT(*) AS number_of_attributes, 'orderdetails' AS name
  FROM pragma_table_info('orderdetails')
 UNION ALL
SELECT COUNT(*) AS number_of_attributes, 'orders' AS name
  FROM pragma_table_info('orders')
 UNION ALL
SELECT COUNT(*) AS number_of_attributes, 'payments' AS name
  FROM pragma_table_info('payments')
 UNION ALL
SELECT COUNT(*) AS number_of_attributes, 'productlines' AS name
  FROM pragma_table_info('productlines')
 UNION ALL
SELECT COUNT(*) AS number_of_attributes, 'products' AS name
  FROM pragma_table_info('products');

--Number of rows in each TABLE

SELECT COUNT(*) AS number_of_rows, 'customers' AS name
  FROM customers
 UNION ALL
SELECT COUNT(*) AS number_of_rows, 'employees' AS name
  FROM employees
 UNION ALL
SELECT COUNT(*) AS number_of_rows, 'offices' AS name
  FROM offices
 UNION ALL
SELECT COUNT(*) AS number_of_rows, 'orderdetails' AS name
  FROM orderdetails
 UNION ALL
SELECT COUNT(*) AS number_of_rows, 'orders' AS name
  FROM orders
 UNION ALL
SELECT COUNT(*) AS number_of_rows, 'payments' AS name
  FROM payments
 UNION ALL
SELECT COUNT(*) AS number_of_rows, 'productlines' AS name
  FROM productlines
 UNION ALL
SELECT COUNT(*) AS number_of_rows, 'products' AS name
  FROM products;

 --Combine the columns
 
  WITH 
       table_names AS (
SELECT name AS table_name
  FROM sqlite_schema
 WHERE type='table'
 ),
       attribute_no AS (
SELECT COUNT(*) AS number_of_attributes, 'customers' AS name
  FROM pragma_table_info('customers')
 UNION ALL
SELECT COUNT(*) AS number_of_attributes, 'employees' AS name
  FROM pragma_table_info('employees')
 UNION ALL
SELECT COUNT(*) AS number_of_attributes, 'offices' AS name
  FROM pragma_table_info('offices')
 UNION ALL
SELECT COUNT(*) AS number_of_attributes, 'orderdetails' AS name
  FROM pragma_table_info('orderdetails')
 UNION ALL
SELECT COUNT(*) AS number_of_attributes, 'orders' AS name
  FROM pragma_table_info('orders')
 UNION ALL
SELECT COUNT(*) AS number_of_attributes, 'payments' AS name
  FROM pragma_table_info('payments')
 UNION ALL
SELECT COUNT(*) AS number_of_attributes, 'productlines' AS name
  FROM pragma_table_info('productlines')
 UNION ALL
SELECT COUNT(*) AS number_of_attributes, 'products' AS name
  FROM pragma_table_info('products')
  ),
       row_numbers AS (
SELECT COUNT(*) AS number_of_rows, 'customers' AS name
  FROM customers
 UNION ALL
SELECT COUNT(*) AS number_of_rows, 'employees' AS name
  FROM employees
 UNION ALL
SELECT COUNT(*) AS number_of_rows, 'offices' AS name
  FROM offices
 UNION ALL
SELECT COUNT(*) AS number_of_rows, 'orderdetails' AS name
  FROM orderdetails
 UNION ALL
SELECT COUNT(*) AS number_of_rows, 'orders' AS name
  FROM orders
 UNION ALL
SELECT COUNT(*) AS number_of_rows, 'payments' AS name
  FROM payments
 UNION ALL
SELECT COUNT(*) AS number_of_rows, 'productlines' AS name
  FROM productlines
 UNION ALL
SELECT COUNT(*) AS number_of_rows, 'products' AS name
  FROM products
  )
  
 SELECT tn.table_name, an.number_of_attributes, number_of_rows
   FROM table_names tn
   JOIN attribute_no AS an ON tn.table_name = an.name
   JOIN row_numbers AS rn ON tn.table_name = rn.name
   
--Question 1: Which products should we order more/less of?
--We'll consider  low stock(i.e. product in demand) and product performance
--a)product performance=SUM(quantityOrdered × priceEach)

SELECT p.productCode, 
       od.quantityOrdered, od.priceEach,
	   SUM(od.quantityOrdered * od.priceEach) AS revenue
  FROM orderdetails AS od
  LEFT JOIN products AS p ON p.productCode = od.productCode
 GROUP BY p.productCode
 ORDER BY revenue DESC
 LIMIT 10
 
 b) low stock = SUM(quantityOrdered)/quantityInStock 
  
SELECT productCode, 
       ROUND(SUM(quantityOrdered) * 1.0/(SELECT quantityInStock 
	                                       FROM products p 
										  WHERE od.productCode = p.productCode),2) AS low_stock
  FROM orderdetails od
 GROUP BY productCode
 ORDER BY low_stock DESC
 LIMIT 10;
 
 --Combine the product performance &amp; low_stock queries using CTE
 
  WITH
       lowStock AS (
SELECT productCode, 
       ROUND(SUM(quantityOrdered) * 1.0/(SELECT quantityInStock 
	                                       FROM products p 
										  WHERE od.productCode = p.productCode),2) AS low_stock
  FROM orderdetails od
 GROUP BY productCode
 ORDER BY low_stock
 LIMIT 10	   
 ),
       productPerformance AS (
SELECT p.productCode, 
       od.quantityOrdered, od.priceEach,
	   SUM(od.quantityOrdered * od.priceEach) AS revenue
  FROM orderdetails AS od
  LEFT JOIN products AS p ON p.productCode = od.productCode
 GROUP BY p.productCode
 ORDER BY revenue DESC
 )
 
 SELECT p.productCode, p.productName, p.productLine
   FROM productPerformance pp
   JOIN products p ON p.productCode = pp.productCode
  WHERE pp.productCode IN (SELECT productCode
                          FROM lowStock)
  ORDER BY revenue DESC
  LIMIT 10
	   
	 
 --Question 2: How should we match marketing &amp; communication strategies to customer behavior?
 -- We want to find the VIP customers &amp; less engaged customers
 --Ceteris paribus, VIP customers bring in the most profit for the store while Less-engaged customers bring in less profit
 
 SELECT o.customerNumber, 
        od.quantityOrdered, od.priceEach, 
		p.buyPrice,
		SUM(quantityOrdered * (priceEach - buyPrice)) AS profit                                  --sums profit per customer
   FROM orders o
   LEFT JOIN orderdetails od ON o.orderNumber = od.orderNumber
   LEFT JOIN products p ON p.productCode = od.productCode
  GROUP BY o.customerNumber
  ORDER BY profit

 --Finding top5 VIP customers
 
   WITH 
        VIPtop5 AS (
 SELECT o.customerNumber, 
        od.quantityOrdered, od.priceEach, 
		p.buyPrice,
		SUM(quantityOrdered * (priceEach - buyPrice)) AS profit                                 
   FROM orders o
   LEFT JOIN orderdetails od ON o.orderNumber = od.orderNumber
   LEFT JOIN products p ON p.productCode = od.productCode
  GROUP BY o.customerNumber		
		)

SELECT contactLastName, contactFirstName, city, country, customerNumber
  FROM customers
WHERE customerNumber IN (SELECT customerNumber
                           FROM VIPtop5
						  ORDER BY profit DESC
						  LIMIT 5)  
						  				  
--Finding top5 less engaged customers

WITH 
        LessEngaged5 AS (
 SELECT o.customerNumber, 
        od.quantityOrdered, od.priceEach, 
		p.buyPrice,
		SUM(quantityOrdered * (priceEach - buyPrice)) AS profit                                 
   FROM orders o
   LEFT JOIN orderdetails od ON o.orderNumber = od.orderNumber
   LEFT JOIN products p ON p.productCode = od.productCode
  GROUP BY o.customerNumber		
		)

SELECT contactLastName, contactFirstName, city, country, customerNumber
  FROM customers
WHERE customerNumber IN (SELECT customerNumber
                           FROM LessEngaged5
						  ORDER BY profit
						  LIMIT 5)
						  
-- Question 3: How much can we spend on acquiring new customers?
--To begin with, let's find the number of new customers arriving each month. 
--That way we can check if it's worth spending money on acquiring new customers. This query helps to find these numbers

WITH 

payment_with_year_month_table AS (
SELECT *, 
       CAST(SUBSTR(paymentDate, 1,4) AS INTEGER)*100 + CAST(SUBSTR(paymentDate, 6,7) AS INTEGER) AS year_month
  FROM payments p             
),

customers_by_month_table AS (
SELECT p1.year_month, COUNT(*) AS number_of_customers, SUM(p1.amount) AS total
  FROM payment_with_year_month_table p1
 GROUP BY p1.year_month
),

new_customers_by_month_table AS (
SELECT p1.year_month, 
       COUNT(*) AS number_of_new_customers,
       SUM(p1.amount) AS new_customer_total,
       (SELECT number_of_customers
          FROM customers_by_month_table c
        WHERE c.year_month = p1.year_month) AS number_of_customers,
       (SELECT total
          FROM customers_by_month_table c
         WHERE c.year_month = p1.year_month) AS total
  FROM payment_with_year_month_table p1
 WHERE p1.customerNumber NOT IN (SELECT customerNumber
                                   FROM payment_with_year_month_table p2
                                  WHERE p2.year_month &lt; p1.year_month)
 GROUP BY p1.year_month
)

SELECT year_month, 
       ROUND(number_of_new_customers*100/number_of_customers,1) AS number_of_new_customers_props,
       ROUND(new_customer_total*100/total,1) AS new_customers_total_props
  FROM new_customers_by_month_table;

--To determine how much money we can spend acquiring new customers, we can compute the Customer Lifetime Value (LTV).
--It represents the average amount of money a customer generates. 
--We can then determine how much we can spend on marketing.

  WITH 
       profit_per_customer AS (
 SELECT o.customerNumber, 
        od.quantityOrdered, od.priceEach, 
		p.buyPrice,
		SUM(quantityOrdered * (priceEach - buyPrice)) AS profit                                  --sums profit per customer
   FROM orders o
   LEFT JOIN orderdetails od ON o.orderNumber = od.orderNumber
   LEFT JOIN products p ON p.productCode = od.productCode
  GROUP BY o.customerNumber
  ORDER BY profit
  )
 SELECT AVG(profit)
   FROM profit_per_customer 

-- We can use LTV to predict our future profit. 
--So, if we get ten new customers next month, we'll earn 390,395 dollars, and we can decide based on this prediction how much we can spend on acquiring new customers.</sql><current_tab id="0"/></tab_sql></sqlb_project>