Tugas Sql

-- 3. Tampilkan sale_hp untuk semua tahun, tp hanya yang top year di tahun bersangkutan
-- EXISTS
SELECT A.*, B.hp_name FROM sale_hp A
INNER JOIN product_hp B ON A.hp_id = B.hp_id
WHERE EXISTS (

        SELECT 1 FROM top_hp_of_the_year Z
        WHERE A.year = Z.year
        AND A.hp_id = Z.hp_id

);

-- IN
SELECT A.*, B.hp_name FROM sale_hp A
INNER JOIN product_hp B ON A.hp_id = B.hp_id
WHERE year IN ( SELECT year FROM top_hp_of_the_year Z WHERE A.hp_id = Z.hp_id );

-- 4. Tampilkan sale hp perbrand brand yang tidak semua typenya terjual di tahun bersangkutan
-- mapping brand terjual di tahun apa aja
WITH year_brand AS (
    SELECT A.year, C.brand_id, C.brand_name
    FROM sale_hp A
    INNER JOIN product_hp B ON A.hp_id = B.hp_id
    INNER JOIN brand_hp C ON B.hp_brand_id = C.brand_id
    GROUP BY A.year, C.brand_id, C.brand_name
)

-- mapping, dari brand tersebut ada produk apa saja
-- dan ambil hanya yang pernah tidak terjual saja
SELECT B.year, B.brand_name FROM product_hp A
INNER JOIN year_brand B ON A.hp_brand_id = B.brand_id
WHERE
NOT EXISTS (
    SELECT 1 FROM sale_hp Z
    WHERE A.hp_id = Z.hp_id
    AND Z.year = B.year
)
ORDER BY B.year;

-- Without WITH
SELECT A.year, C.brand_name
FROM sale_hp A
INNER JOIN product_hp B ON A.hp_id = B.hp_id
INNER JOIN brand_hp C ON B.hp_brand_id = C.brand_id
INNER JOIN product_hp D ON C.brand_id = D.hp_brand_id
WHERE NOT EXISTS(
    SELECT 1 FROM sale_hp Z
    WHERE D.hp_id = Z.hp_id
    AND A.year = Z.year
)
GROUP BY A.year, C.brand_id
ORDER BY A.year;

-- 5. Tampilkan sale hp perbrand yg total penjualannya meningkat dari tahun sebelumnya
-- Have no idea without WITH, asumsi nya data penjualan akan terus bertambah dari tahun ke tahun, dan di suruh menampilkan data dengan penjualan meningkat dari tahun sebelum nya, tanpa ada inputan / pantekan tahun.

-- Data penjualan untuk semua brand, dan semua tahun
WITH data_penjualan AS (
    SELECT A.year, C.brand_id, C.brand_name, SUM(A.qty) total_penjualan
    FROM sale_hp A
    INNER JOIN product_hp B ON A.hp_id = B.hp_id
    INNER JOIN brand_hp C ON B.hp_brand_id = C.brand_id
    GROUP BY A.year, C.brand_id, C.brand_name
),

-- ambil data penjualan naik
data_penjualan_naik AS (
    SELECT A.year, A.brand_id, A.brand_name, A.total_penjualan FROM data_penjualan A
    WHERE EXISTS (
        SELECT 1 FROM data_penjualan Z
        WHERE A.year::bigint = Z.year::bigint +1
        AND A.brand_id = Z.brand_id
        AND A.total_penjualan > Z.total_penjualan
    )
)

-- ambil data penjualan naik dengan data penjualan sebelum nya, dan cari selisihnya
SELECT A.year, A.brand_name, A.total_penjualan, B.total_penjualan AS total_penjualan_thn_sebelumnya,
A.total_penjualan - B.total_penjualan AS selisih_penjualan
FROM data_penjualan_naik A
INNER JOIN data_penjualan B ON A.brand_id = B.brand_id AND A.year::bigint - 1 = B.year::bigint ;

-- 6. Tampilkan sale hp per brand yg jumlah penjualan lebih tinggi dari rata2 tiap tahunnya
-- Data penjualan untuk semua brand, dan semua tahun
WITH data_penjualan AS (
    SELECT A.year, C.brand_id, C.brand_name, SUM(A.qty) total_penjualan
    FROM sale_hp A
    INNER JOIN product_hp B ON A.hp_id = B.hp_id
    INNER JOIN brand_hp C ON B.hp_brand_id = C.brand_id
    GROUP BY A.year, C.brand_id, C.brand_name
),
-- Rata rata per tahun
data_avg_penjualan AS (
SELECT A.year, AVG(A.total_penjualan) avg_penjualan FROM data_penjualan A
GROUP BY A.year
)
-- Ambil data penjualan yang penjualan nya lebih tinggi dari rata - rata per tahun nya
SELECT A.year, A.brand_name, A.total_penjualan, B.avg_penjualan AS rata_rata_penjualan_tiap_tahun FROM data_penjualan A
INNER JOIN data_avg_penjualan B ON A.year = B.year
WHERE A.total_penjualan > B.avg_penjualan
ORDER BY A.year;