queries.sql

CREATE DATABASE techvantage_crm;
USE techvantage_crm;
CREATE TABLE IF NOT EXISTS accounts (
    account         VARCHAR(100),
    sector          VARCHAR(100),
    year_established INT,
    revenue         DECIMAL(12,2),
    employees       INT,
    office_location VARCHAR(100),
    subsidiary_of   VARCHAR(100)
);

CREATE TABLE IF NOT EXISTS products (
    product     VARCHAR(100),
    series      VARCHAR(100),
    sales_price DECIMAL(10,2)
);

CREATE TABLE IF NOT EXISTS sales_teams (
    sales_agent     VARCHAR(100),
    manager         VARCHAR(100),
    regional_office VARCHAR(100)
);
 
CREATE TABLE IF NOT EXISTS sales_pipeline (
    opportunity_id VARCHAR(20),
    sales_agent    VARCHAR(100),
    product        VARCHAR(100),
    account        VARCHAR(100),
    deal_stage     VARCHAR(50),
    engage_date    DATE,
    close_date     DATE,
    close_value    DECIMAL(10,2)
);

SELECT COUNT(*) FROM sales_pipeline;   
SELECT COUNT(*) FROM accounts;         
SELECT COUNT(*) FROM products;        
SELECT COUNT(*) FROM sales_teams;     

-- Q1
SELECT COUNT(*) AS total_opportunities
FROM sales_pipeline;

TRUNCATE TABLE sales_pipeline;

SHOW VARIABLES LIKE 'secure_file_priv';

LOAD DATA INFILE 'C:/ProgramData/MySQL/MySQL Server 9.7/Uploads/sales_pipeline.csv'
INTO TABLE sales_pipeline
FIELDS TERMINATED BY ','
ENCLOSED BY '"'
LINES TERMINATED BY '\r\n'
IGNORE 1 ROWS
(opportunity_id, sales_agent, product, account, deal_stage,
 @engage_date, @close_date, @close_value)
SET
  engage_date = NULLIF(@engage_date, ''),
  close_date  = NULLIF(@close_date, ''),
  close_value = IF(@close_value = '' OR @close_value IS NULL, NULL, CAST(@close_value AS DECIMAL(10,2)));
  
-- Q2
SELECT DISTINCT deal_stage
FROM sales_pipeline;

-- Q3
SELECT COUNT(*) AS total_won_deals
FROM sales_pipeline
WHERE deal_stage = 'Won';

-- Q4
SELECT COUNT(*) AS active_deals
FROM sales_pipeline
WHERE deal_stage NOT IN ('Won', 'Lost');

-- Q5
SELECT COUNT(*) AS high_value_won_deals
FROM sales_pipeline
WHERE deal_stage = 'Won'
  AND close_value > 10000;
  
-- Q6
SELECT
    SUM(close_value)  AS total_revenue,
    AVG(close_value)  AS avg_deal_value
FROM sales_pipeline
WHERE deal_stage = 'Won';

-- Q7
SELECT
    sales_agent,
    COUNT(*) AS won_deals
FROM sales_pipeline
WHERE deal_stage = 'Won'
GROUP BY sales_agent
ORDER BY won_deals DESC
LIMIT 5;

-- Q8
SELECT
    sales_agent,
    SUM(close_value) AS total_revenue
FROM sales_pipeline
WHERE deal_stage = 'Won'
GROUP BY sales_agent
ORDER BY total_revenue DESC
LIMIT 5;

-- Q9
SELECT
    sales_agent,
    COUNT(*) AS total_deals,
    COUNT(CASE WHEN deal_stage = 'Won' THEN 1 END) AS won_deals
FROM sales_pipeline
GROUP BY sales_agent
ORDER BY won_deals DESC;

-- Q10
SELECT
    product,
    SUM(close_value) AS total_revenue
FROM sales_pipeline
WHERE deal_stage = 'Won'
GROUP BY product
ORDER BY total_revenue DESC;

-- Q11
SELECT
    deal_stage,
    COUNT(*) AS deal_count,
    ROUND(COUNT(*) * 100.0 / (SELECT COUNT(*) FROM sales_pipeline), 2) AS percentage
FROM sales_pipeline
GROUP BY deal_stage
ORDER BY deal_count DESC;

-- Q12
SELECT 
    YEAR(close_date) AS close_year,
    COUNT(*) AS deals_closed
FROM sales_pipeline
WHERE deal_stage IN ('Won', 'Lost')
  AND close_date IS NOT NULL
GROUP BY close_year
ORDER BY close_year ASC;

-- Q13
SELECT 
    MONTH(close_date) AS close_month,
    COUNT(*) AS deals_closed
FROM sales_pipeline
WHERE deal_stage IN ('Won', 'Lost')
  AND close_date IS NOT NULL
GROUP BY close_month
ORDER BY deals_closed DESC;

-- Q14
SELECT
    AVG(DATEDIFF(close_date, engage_date)) AS avg_days_to_close
FROM sales_pipeline
WHERE deal_stage = 'Won'
  AND engage_date IS NOT NULL
  AND close_date IS NOT NULL;
  
-- Q15
SELECT
    CASE
        WHEN close_value < 1000 THEN 'Small'
        WHEN close_value BETWEEN 1000 AND 5000 THEN 'Medium'
        ELSE 'Large'
    END AS deal_size,
    COUNT(*) AS deal_count
FROM sales_pipeline
WHERE deal_stage = 'Won'
GROUP BY deal_size
ORDER BY deal_count DESC;

-- Q16
SELECT
    sales_agent,
    COUNT(*) AS won_deals,
    CASE
        WHEN COUNT(*) > 60 THEN 'Top Performer'
        WHEN COUNT(*) BETWEEN 30 AND 60 THEN 'Solid Performer'
        ELSE 'Developing'
    END AS performance_tier
FROM sales_pipeline
WHERE deal_stage = 'Won'
GROUP BY sales_agent
ORDER BY won_deals DESC;

-- Q17
SELECT COUNT(*) AS above_avg_won_deals
FROM sales_pipeline
WHERE deal_stage = 'Won'
  AND close_value > (
        SELECT AVG(close_value)
        FROM sales_pipeline
        WHERE deal_stage = 'Won'
      );
      
-- Q18
SELECT
    sales_agent,
    COUNT(*) AS won_deals
FROM sales_pipeline
WHERE deal_stage = 'Won'
GROUP BY sales_agent
HAVING COUNT(*) > (
    SELECT AVG(agent_wins)
    FROM (
        SELECT COUNT(*) AS agent_wins
        FROM sales_pipeline
        WHERE deal_stage = 'Won'
        GROUP BY sales_agent
    ) AS subq
)
ORDER BY won_deals DESC;

-- Q19
SELECT
    sector,
    COUNT(*) AS account_count
FROM accounts
GROUP BY sector
HAVING COUNT(*) > 10
ORDER BY account_count DESC;

-- Q20
SELECT
    product,
    series,
    sales_price
FROM products
WHERE sales_price > (
    SELECT AVG(sales_price)
    FROM products
)
ORDER BY sales_price DESC;