SQL - Introduction to Aggregation and Subquery Script by HadiMaster

-- Create the marketing dataset table
CREATE TABLE marketing_data (
    customer_id SERIAL PRIMARY KEY,
    age INT,
    gender VARCHAR(10),
    income INT,
    education_level VARCHAR(50),
    purchase_amount INT
);

-- Insert data into the marketing dataset table
INSERT INTO marketing_data (age, gender, income, education_level, purchase_amount) VALUES
(35, 'Male', 50000, 'Bachelor''s Degree', 100),
(28, 'Female', 60000, 'Master''s Degree', 150),
(42, 'Male', 75000, 'High School', 200),
(38, 'Female', 55000, 'Bachelor''s Degree', 120),
(45, 'Male', 80000, 'PhD', 180),
(31, 'Female', 48000, 'High School', 90),
(26, 'Male', 40000, 'High School', 80),
(29, 'Female', 65000, 'Bachelor''s Degree', 130),
(33, 'Male', 70000, 'Master''s Degree', 160),
(36, 'Female', 58000, 'Bachelor''s Degree', 140),
(40, 'Male', 72000, 'Bachelor''s Degree', 170),
(27, 'Female', 52000, 'High School', 110),
(34, 'Male', 68000, 'Master''s Degree', 150),
(39, 'Female', 73000, 'Bachelor''s Degree', 200),
(32, 'Male', 50000, 'High School', 100),
(37, 'Female', 60000, 'Master''s Degree', 170),
(43, 'Male', 78000, 'Bachelor''s Degree', 190),
(30, 'Female', 54000, 'High School', 120),
(44, 'Male', 82000, 'PhD', 210),
(25, 'Female', 45000, 'Bachelor''s Degree', 80),
(29, 'Male', 67000, 'Master''s Degree', 160),
(35, 'Female', 57000, 'Bachelor''s Degree', 130),
(41, 'Male', 74000, 'Bachelor''s Degree', 180),
(28, 'Female', 51000, 'High School', 100),
(33, 'Male', 69000, 'Master''s Degree', 150);

-- contoh agregasi data menggunakan group by

SELECT education_level, COUNT(*) AS num_customers
FROM marketing_data
GROUP BY education_level;

SELECT SUM(purchase_amount) as total_purchase
FROM marketing_data;

SELECT gender, AVG(purchase_amount) AS avg_purchase_amount
FROM marketing_data
GROUP BY gender;

SELECT gender, MAX(income) AS max_income, MIN(income) as min_income
FROM marketing_data
GROUP BY gender;

-- penggunaan subquery

-- hitung jumlah total purchase untuk customer yang memiliki income lebih dari 60000
SELECT total_purchase_amount
FROM (
    SELECT SUM(purchase_amount) AS total_purchase_amount
    FROM marketing_data
    WHERE income > 60000
) AS subquery;

-- mencari pelanggan yang memiliki purchase amount di atas rata-rata
SELECT *
FROM marketing_data
WHERE purchase_amount > (
    SELECT AVG(purchase_amount)
    FROM marketing_data
);

-- mencari customer yang memiliki income tertinggi
SELECT *
FROM marketing_data
WHERE income = (
    SELECT MAX(income)
    FROM marketing_data
);

-- with syntax

-- hitung jumlah total purchase untuk customer yang memiliki income lebih dari 60000
WITH subquery AS (
    SELECT SUM(purchase_amount) AS total_purchase_amount
    FROM marketing_data
    WHERE income > 60000
)
SELECT total_purchase_amount
FROM subquery;

-- mencari pelanggan yang memiliki purchase amount di atas rata-rata
WITH avg_income AS (
    SELECT AVG(income) AS average_income
    FROM marketing_data
)
SELECT *
FROM marketing_data
WHERE income > (SELECT average_income FROM avg_income);