I did data analysis related to Marketing, Operations, and Finance domains. It is an effort to use SQL tools to do production and sales assessments, answer necessary questions, and give a better insight for future decisions.

The database has five tables including:

  1. web_events
  2. orders
  3. accounts
  4. sales_reps
  5. region

The Entity Relationship Diagram (ERD) for tables is as below:

Through this section, I shared some of my efforts to answer analytics questions using PostgreSQL codes.

Analytical Question: For the region with the largest sales, how many total orders were placed?

WITH t1 AS (
   SELECT r.name reg_name, SUM(o.total_amt_usd) total_usd    
   FROM sales_reps s  
   JOIN region r 
   ON r.id = s.region_id
   JOIN accounts a 
   ON s.id = a.sales_rep_id
   JOIN orders o 
   ON a.id = o.account_id 
   GROUP BY reg_name
   ORDER BY total_usd DESC
   LIMIT 1),

t2 AS (
   SELECT r.name reg_name, COUNT(o.total) total_order    
   FROM sales_reps s  
   JOIN region r 
   ON r.id = s.region_id
   JOIN accounts a 
   ON s.id = a.sales_rep_id
   JOIN orders o 
   ON a.id = o.account_id 
   GROUP BY reg_name)

SELECT t1.reg_name, t1.total_usd, t2.total_order
FROM t1
JOIN t2
ON t1.reg_name = t2.reg_name;

Answer:
Region name: Northeast
Total sales amount (usd): 7744405.36
Total order: 2357

This page will be updated with more analysis.