Pharma.sql

create database pharma;

-- Pharmaceutical Manufacturing Process Analytics using SQL Server
-- Check NULL Values

SELECT
    COUNT(*) AS TotalRows,
    SUM(CASE WHEN Humidity IS NULL THEN 1 ELSE 0 END) AS Humidity_Nulls
FROM Pharma_Process;

-- Full column-level NULL audit

SELECT
    SUM(CASE WHEN BatchID IS NULL THEN 1 ELSE 0 END) AS BatchID_Null,
    SUM(CASE WHEN ProductName IS NULL THEN 1 ELSE 0 END) AS ProductName_Null,
    SUM(CASE WHEN Stage IS NULL THEN 1 ELSE 0 END) AS Stage_Null,
    SUM(CASE WHEN InputWeight_kg IS NULL THEN 1 ELSE 0 END) AS Input_Null,
    SUM(CASE WHEN OutputWeight_kg IS NULL THEN 1 ELSE 0 END) AS Output_Null,
    SUM(CASE WHEN Duration_hr IS NULL THEN 1 ELSE 0 END) AS Duration_Null,
    SUM(CASE WHEN RoomTemp_C IS NULL THEN 1 ELSE 0 END) AS Temp_Null,
    SUM(CASE WHEN Humidity IS NULL THEN 1 ELSE 0 END) AS Humidity_Null
FROM Pharma_Process;

-- Handle NULL Humidity (Replace with Stage-wise average)

UPDATE p
SET Humidity = avg_tbl.avg_humidity
FROM Pharma_Process p
JOIN (
    SELECT Stage, AVG(Humidity) AS avg_humidity
    FROM Pharma_Process
    WHERE Humidity IS NOT NULL
    GROUP BY Stage
) avg_tbl
ON p.Stage = avg_tbl.Stage
WHERE p.Humidity IS NULL;

-- BASIC SQL QUERIES

SELECT * FROM Pharma_Process;

-- 1. Unique products

SELECT DISTINCT ProductName FROM Pharma_Process;


-- INTERMEDIATE

-- 2. Yield Loss Calculation

SELECT
    BatchID,
    ProductName,
    Stage,
    InputWeight_kg,
    OutputWeight_kg,
    (InputWeight_kg - OutputWeight_kg) AS Weight_Loss_kg
FROM Pharma_Process;


-- 3. Average Loss by Stage

SELECT
    Stage,
    AVG(InputWeight_kg - OutputWeight_kg) AS Avg_Loss_kg
FROM Pharma_Process
GROUP BY Stage;

-- 4. Deviation Count per Product

SELECT
    ProductName,
    COUNT(*) AS Deviation_Count
FROM Pharma_Process
WHERE DeviationType <> 'Normal run'
GROUP BY ProductName;

-- ADVANCED

select * from Pharma_Process

-- 5. Yield Percentage

SELECT
    BatchID,
    Stage,
    ROUND((OutputWeight_kg / InputWeight_kg) * 100, 2) AS Yield_Percentage
FROM Pharma_Process;

-- 6. Worst Performing Machines

SELECT
    MachineID,
    AVG(InputWeight_kg - OutputWeight_kg) AS Avg_Loss
FROM Pharma_Process
GROUP BY MachineID
ORDER BY Avg_Loss DESC;

-- 7. Operator Performance (Deviation Impact)

SELECT
    Operator,
    COUNT(*) AS Total_Operations,
    SUM(CASE WHEN DeviationType <> 'Normal run' THEN 1 ELSE 0 END) AS Deviations
FROM Pharma_Process
GROUP BY Operator;

-- 8. Environmental Impact Analysis

SELECT
    Stage,
    AVG(RoomTemp_C) AS Avg_Temp,
    AVG(Humidity) AS Avg_Humidity,
    AVG(InputWeight_kg - OutputWeight_kg) AS Avg_Loss
FROM Pharma_Process
GROUP BY Stage;


-- 9. Which batch–stage combination has the highest material loss %? 
-- (Tests: arithmetic logic + business understanding)

SELECT
    BatchID,
    ProductName,
    Stage,
    ROUND(((InputWeight_kg - OutputWeight_kg) / InputWeight_kg) * 100, 2) AS Loss_Percentage
FROM Pharma_Process
ORDER BY Loss_Percentage DESC;

--10. Identify products with inconsistent yield (high variance)

SELECT
    ProductName,
    VAR(InputWeight_kg - OutputWeight_kg) AS Loss_Variance
FROM Pharma_Process
GROUP BY ProductName
ORDER BY Loss_Variance DESC;

-- PROCESS & QUALITY CONTROL QUESTIONS

-- 11. Which deviation types cause maximum loss?
-- (Quality + root cause analysis)

SELECT
    DeviationType,
    AVG(InputWeight_kg - OutputWeight_kg) AS Avg_Loss
FROM Pharma_Process
WHERE DeviationType IS NOT NULL
GROUP BY DeviationType
ORDER BY Avg_Loss DESC;

-- 12. Find machines responsible for repeated deviations.

SELECT
    MachineID,
    COUNT(*) AS Deviation_Count
FROM Pharma_Process
WHERE DeviationType <> 'Normal run'
GROUP BY MachineID
HAVING COUNT(*) > 1;


-- EXPERT

select * from Pharma_Process;

-- 13. Rank batches by efficiency

SELECT *,
       RANK() OVER (ORDER BY (OutputWeight_kg / InputWeight_kg) DESC) AS Efficiency_Rank
FROM Pharma_Process;

-- ENVIRONMENT & SENSOR DATA QUESTIONS 

-- 14. Does high humidity lead to higher loss?

SELECT
    CASE 
        WHEN Humidity < 45 THEN 'Low Humidity'
        WHEN Humidity BETWEEN 45 AND 55 THEN 'Normal Humidity'
        ELSE 'High Humidity'
    END AS Humidity_Level,
    AVG(InputWeight_kg - OutputWeight_kg) AS Avg_Loss
FROM Pharma_Process
GROUP BY
    CASE 
        WHEN Humidity < 45 THEN 'Low Humidity'
        WHEN Humidity BETWEEN 45 AND 55 THEN 'Normal Humidity'
        ELSE 'High Humidity'
    END;

-- 15. Identify outlier batches (loss > 1.5× stage average)
-- Outlier detection using pure SQL

WITH stage_avg AS (
    SELECT Stage, AVG(InputWeight_kg - OutputWeight_kg) AS avg_loss
    FROM Pharma_Process
    GROUP BY Stage
)
SELECT p.*
FROM Pharma_Process p
JOIN stage_avg s ON p.Stage = s.Stage
WHERE (p.InputWeight_kg - p.OutputWeight_kg) > 1.5 * s.avg_loss;


-- WINDOW FUNCTION

-- 16. Rank operators within each stage by efficiency (PARTITION BY)

SELECT
    Stage,
    Operator,
    ROUND((OutputWeight_kg / InputWeight_kg) * 100, 2) AS Yield_Percentage,
    RANK() OVER (PARTITION BY Stage ORDER BY (OutputWeight_kg / InputWeight_kg) DESC) AS Rank_In_Stage
FROM Pharma_Process;


-- 17. Detect process drift over time

SELECT
    Stage,
    MONTH(Date) AS Month,
    AVG(InputWeight_kg - OutputWeight_kg) AS Avg_Loss
FROM Pharma_Process
GROUP BY Stage, MONTH(Date)
ORDER BY Stage, Month;

-- 18. Create a KPI-ready view for dashboards
-- Power BI / Tableau integration mindset

CREATE VIEW Manufacturing_KPIs AS
SELECT
    ProductName,
    Stage,
    AVG(InputWeight_kg - OutputWeight_kg) AS Avg_Loss,
    AVG((OutputWeight_kg / InputWeight_kg) * 100) AS Avg_Yield
FROM Pharma_Process
GROUP BY ProductName, Stage;


SELECT *
FROM Manufacturing_KPIs;

-- Filter the VIEW
-- only Coating stage

SELECT *
FROM Manufacturing_KPIs
WHERE Stage = 'Coating';

-- Sort the results

SELECT *
FROM Manufacturing_KPIs
ORDER BY Avg_Loss DESC;

-- Use the VIEW in JOINs (join KPI view with raw data)

SELECT
    p.BatchID,
    p.Operator,
    v.ProductName,
    v.Stage,
    v.Avg_Loss,
    v.Avg_Yield
FROM Pharma_Process p
JOIN Manufacturing_KPIs v
    ON p.ProductName = v.ProductName
   AND p.Stage = v.Stage;

-- See the VIEW definition

EXEC sp_helptext 'Manufacturing_KPIs';

-- MATERIALIZED KPI TABLE
-- Instead of a dynamic view, create a physical KPI table:

SELECT
    ProductName,
    Stage,
    AVG(InputWeight_kg - OutputWeight_kg) AS Avg_Loss,
    AVG((OutputWeight_kg / InputWeight_kg) * 100) AS Avg_Yield
INTO Pharma_KPI_Snapshot
FROM Pharma_Process
GROUP BY ProductName, Stage;

-- ML-ready features

CREATE VIEW ML_Features AS
SELECT
    BatchID,
    Stage,
    (InputWeight_kg - OutputWeight_kg) AS Loss_kg,
    (OutputWeight_kg / InputWeight_kg) * 100 AS Yield_pct,
    RoomTemp_C,
    Humidity
FROM Pharma_Process;