temp

1. Beda IN dan EXITS
    - IN = adalah untuk mengecek apakah nilai termasuk dalam list
              jika nilai nya terdapat dalam list/sebuah array maka akan menghasilkan nilai t = true
    - EXISTS = adalah untuk memeriksa apakah row nya ada atau tidak

2. Kapan pakai IN Kapan pakai EXISTS
    a. IN = jika kita ingin memfilter lebih dari satu value.
    b. b.EXISTS = jika kita ingin memfilter yang datanya ada dibaris2 table saja / data nya yg ada didalam subquery.

3. Tampilkan sale_hp untuk semua tahun, tp hanya yang top year di tahun bersangkutan
    a. SELECT A.hp_id, C.brand_id, A.year, B.hp_name, 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
        WHERE EXISTS (SELECT 1 FROM top_hp_of_the_year Z WHERE A.hp_id = Z.hp_id AND A.year = Z.year)
        ORDER BY A.year;

    b. SELECT A.hp_id, C.brand_id, A.year, B.hp_name, 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
        WHERE A.hp_id IN (SELECT Z.hp_id FROM top_hp_of_the_year Z WHERE A.hp_id = Z.hp_id AND A.year = Z.year)
        ORDER BY A.year;

    c. SELECT A.hp_id, D.brand_id, A.year, C.hp_name, D.brand_name
        FROM top_hp_of_the_year A
        INNER JOIN sale_hp B ON A.hp_id = B.hp_id AND A.year = B.year
        INNER JOIN product_hp C ON B.hp_id = C.hp_id
        INNER JOIN brand_hp D ON C.hp_brand_id = D.brand_id
        ORDER BY A.year ASC;

4. Tampilkan sale hp perbrand brand yang tidak semua typenya terjual di tahun bersangkutan
    a. SELECT temp_year.year, B.brand_name
        FROM product_hp A
        INNER JOIN brand_hp B ON A.hp_brand_id = B.brand_id, (SELECT year FROM sale_hp GROUP BY year) AS temp_year
        WHERE NOT EXISTS (SELECT 1 FROM sale_hp Z WHERE A.hp_id = Z.hp_id AND Z.year = temp_year.year)
        ORDER BY temp_year.year;
    b. SELECT temp_year.year, B.brand_name
        FROM product_hp A
        INNER JOIN brand_hp B ON A.hp_brand_id = B.brand_id, (SELECT year FROM sale_hp GROUP BY year) AS temp_year
        WHERE A.hp_id NOT IN (SELECT Z.hp_id FROM sale_hp Z WHERE A.hp_id = Z.hp_id AND Z.year = temp_year.year)
        ORDER BY temp_year.year;

5. Tampilkan sale hp perbrand yg total penjualannya meningkat dari tahun sebelumnya
    WITH TEMP AS (
        SELECT B.hp_id, C.brand_name, A.year,SUM(A.qty) AS qty_sale,CAST(A.year::bigint - 1 AS CHARACTER VARYING) AS temp_year,
            (SELECT COALESCE(SUM(Z.qty),0)
             FROM sale_hp Z
             WHERE Z.hp_id = B.hp_id
                    AND Z.year IN (CAST(A.year::bigint - 1 AS CHARACTER VARYING))) AS temp_qty_sale
            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, B.hp_id, C.brand_name
            ORDER BY A.year, temp_year ASC
    )
    SELECT X.brand_name, X.temp_year AS year, X.qty_sale-X.temp_qty_sale AS qty_sale_difference
    FROM TEMP X
    WHERE X.temp_year <> (SELECT MIN(Z.temp_year) FROM TEMP Z)
    AND X.qty_sale-X.temp_qty_sale > 0
    GROUP BY X.brand_name, X.temp_year, X.qty_sale, X.temp_qty_sale
    ORDER BY X.temp_year ASC;

6. Tampilkan sale hp per brand yg jumlah penjualan lebih tinggi dari rata2 tiap tahunnya
    SELECT A.year, C.brand_id, C.brand_name,SUM(A.qty) AS qty_sale, ROUND(AVG(A.qty)) AS total_average
    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_name, C.brand_id
    HAVING SUM(A.qty) > ROUND(AVG(A.qty))