DB: Kasus penjualan terbesar

####### ur yang bisa dicoba: https://www.db-fiddle.com/f/2stBSRHozGxh2MPcZHoJoM/0

####### SCHEMA ###############
CREATE TABLE `ms_produk` (
  `no_urut` INT(11) DEFAULT NULL,
  `kode_produk` VARCHAR(13) CHARACTER SET utf8 DEFAULT NULL,
  `nama_produk` VARCHAR(36) CHARACTER SET utf8 DEFAULT NULL,
  `harga` INT(11) DEFAULT NULL
);

CREATE TABLE `tr_penjualan_detail` (
  `kode_transaksi` VARCHAR(16) CHARACTER SET utf8 DEFAULT NULL,
  `kode_produk` VARCHAR(13) CHARACTER SET utf8 DEFAULT NULL,
  `qty` INT(11) DEFAULT NULL,
  `harga_satuan` INT(11) DEFAULT NULL
) ENGINE=InnoDB DEFAULT CHARSET=latin1;
#######`ms_produk`
ALTER TABLE `ms_produk`
  ADD PRIMARY KEY (`no_urut`),
  ADD UNIQUE KEY `kode_produk` (`kode_produk`),
  ADD KEY `nama_produk` (`nama_produk`);
ALTER TABLE `ms_produk` CHANGE `no_urut` `no_urut` INT(11) NOT NULL AUTO_INCREMENT;
#######`tr_penjualan_detail`
ALTER TABLE `tr_penjualan_detail`
  ADD KEY `kode_transaksi` (`kode_transaksi`),
  ADD KEY `kode_produk` (`kode_produk`);

########`ms_produk`
INSERT INTO `ms_produk` (`no_urut`, `kode_produk`, `nama_produk`, `harga`) VALUES
(1, 'prod-01', 'Kotak Pensil DQLab', 62500),
(2, 'prod-02', 'Flashdisk DQLab 64 GB', 55000),
(3, 'prod-03', 'Gift Voucher DQLab 100rb', 100000),
(4, 'prod-04', 'Flashdisk DQLab 32 GB', 40000),
(5, 'prod-05', 'Gift Voucher DQLab 250rb', 250000),
(6, 'prod-06', 'Pulpen Multifunction + Laser DQLab', 92500),
(7, 'prod-07', 'Tas Travel Organizer DQLab', 48000),
(8, 'prod-08', 'Gantungan Kunci DQLab', 15800),
(9, 'prod-09', 'Buku Planner Agenda DQLab', 92000),
(10, 'prod-10', 'Sticky Notes DQLab 500 sheets', 55000);

######`tr_penjualan_detail`
INSERT INTO `tr_penjualan_detail` (`kode_transaksi`, `kode_produk`, `qty`, `harga_satuan`) VALUES
('tr-0001', 'prod-04', 3, 40000),
('tr-0001', 'prod-02', 1, 55000),
('tr-0002', 'prod-08', 2, 15800),
('tr-0003', 'prod-10', 1, 55000),
('tr-0004', 'prod-09', 1, 92000),
('tr-0005', 'prod-06', 1, 92500),
('tr-0006', 'prod-08', 2, 15800),
('tr-0007', 'prod-08', 2, 15800),
('tr-0008', 'prod-07', 1, 50000),
('tr-0009', 'prod-01', 2, 62500),
('tr-0010', 'prod-04', 3, 48000),
('tr-0010', 'prod-08', 1, 15800),
('tr-0010', 'prod-04', 1, 40000);


##############QUERY#############################
SELECT p.kode_produk,p.nama_produk, SUM(qty) tot FROM `ms_produk` p
LEFT JOIN tr_penjualan_detail pd ON p.kode_produk = pd.kode_produk
GROUP BY  p.kode_produk,p.nama_produk;

############
SELECT
    tb_prod_sum_0.*
FROM
    (
        SELECT
            p.kode_produk kode,SUM(qty) tot
        FROM
            `ms_produk` p
        LEFT JOIN
            tr_penjualan_detail pd ON p.kode_produk = pd.kode_produk
        GROUP BY
            p.kode_produk,p.nama_produk
    ) tb_prod_sum_0

    HAVING tot=(SELECT MAX(tot) FROM
    (
        SELECT
            p.kode_produk kode,SUM(qty) tot
        FROM
            `ms_produk` p
        LEFT JOIN
            tr_penjualan_detail pd ON p.kode_produk = pd.kode_produk
        GROUP BY
            p.kode_produk,p.nama_produk
    ) tb_prod_sum_1);
    ###########
    EXPLAIN

SELECT
    tb_prod_sum_0.*
FROM
    (
        SELECT
            p.kode_produk kode,SUM(qty) tot
        FROM
            `ms_produk` p
        LEFT JOIN
            tr_penjualan_detail pd ON p.kode_produk = pd.kode_produk
        GROUP BY
            p.kode_produk,p.nama_produk
    ) tb_prod_sum_0

    HAVING tot=(SELECT MAX(tot) FROM
    (
        SELECT
            p.kode_produk kode,SUM(qty) tot
        FROM
            `ms_produk` p
        LEFT JOIN
            tr_penjualan_detail pd ON p.kode_produk = pd.kode_produk
        GROUP BY
            p.kode_produk,p.nama_produk
    ) tb_prod_sum_1);