database bulan terjual

### bisa dicoba di https://www.db-fiddle.com/f/2stBSRHozGxh2MPcZHoJoM/3

###skema data
###########
CREATE TABLE `ms_produk` (
  `no_urut` int(11) NOT NULL,
  `kode_produk` varchar(13) CHARACTER SET utf8 DEFAULT NULL,
  `nama_produk` varchar(36) CHARACTER SET utf8 DEFAULT NULL,
  `harga` int(11) DEFAULT NULL
);
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);
##############
CREATE TABLE `tr_penjualan` (
  `id` int(11) NOT NULL,
  `kode_transaksi` char(12) NOT NULL,
  `total_product` int(11) NOT NULL,
  `created_at` datetime NOT NULL DEFAULT current_timestamp(),
  `updated_at` timestamp NULL DEFAULT NULL ON UPDATE current_timestamp(),
  `deleted_at` datetime DEFAULT NULL
) ;

INSERT INTO `tr_penjualan` (`id`, `kode_transaksi`, `total_product`, `created_at`, `updated_at`, `deleted_at`) VALUES
(1, 'tr-0001', 2, '2022-04-18 01:03:17', NULL, NULL),
(2, 'tr-0002', 1, '2022-03-18 02:03:17', NULL, NULL),
(3, 'tr-0003', 1, '2022-01-17 04:03:17', NULL, NULL),
(4, 'tr-0004', 1, '2022-05-16 03:03:17', NULL, NULL),
(5, 'tr-0005', 1, '2022-02-11 05:03:17', NULL, NULL),
(6, 'tr-0006', 1, '2022-03-21 07:03:17', NULL, NULL),
(7, 'tr-0007', 1, '2022-04-25 09:03:17', NULL, NULL),
(8, 'tr-0008', 1, '2022-05-5 08:03:17', NULL, NULL),
(9, 'tr-0009', 1, '2022-05-7 11:03:17', NULL, NULL),
(10, 'tr-0010', 3, '2022-02-9 21:03:17', NULL, NULL);
#########

CREATE TABLE `tr_penjualan_detail` (
  `id` int(11) NOT NULL,
  `kode_transaksi` char(12) 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,
  `created_at` datetime NOT NULL DEFAULT current_timestamp(),
  `updated_at` timestamp NULL DEFAULT NULL ON UPDATE current_timestamp()
);
INSERT INTO `tr_penjualan_detail` (`id`, `kode_transaksi`, `kode_produk`, `qty`, `harga_satuan`, `created_at`, `updated_at`) VALUES
(1, 'tr-0001', 'prod-04', 3, 40000, '2022-04-08 01:03:17', '2022-05-07 18:03:31'),
(2, 'tr-0001', 'prod-02', 1, 55000, '2022-05-08 01:03:17', NULL),
(3, 'tr-0002', 'prod-08', 2, 15800, '2022-03-08 01:03:17', '2022-05-07 18:03:39'),
(4, 'tr-0003', 'prod-10', 1, 55000, '2022-05-08 01:03:17', NULL),
(84, 'tr-0004', 'prod-10', 1, 55000, '2022-05-08 01:03:17', NULL),
(5, 'tr-0004', 'prod-09', 1, 92000, '2022-05-08 01:03:17', NULL),
(6, 'tr-0005', 'prod-06', 1, 92500, '2022-05-08 01:03:17', NULL),
(7, 'tr-0006', 'prod-08', 2, 15800, '2022-03-08 01:03:17', '2022-05-07 18:03:47'),
(8, 'tr-0007', 'prod-08', 2, 15800, '2022-04-08 01:03:17', '2022-05-07 18:04:10'),
(9, 'tr-0008', 'prod-07', 1, 50000, '2022-05-08 01:03:17', NULL),
(10, 'tr-0009', 'prod-01', 2, 62500, '2022-05-08 01:03:17', NULL),
(11, 'tr-0010', 'prod-04', 3, 48000, '2022-04-08 01:03:17', '2022-05-07 18:03:53'),
(12, 'tr-0010', 'prod-08', 1, 15800, '2022-05-08 01:03:17', NULL),
(13, 'tr-0002', 'prod-04', 1, 40000, '2022-02-08 01:03:17', '2022-05-07 18:04:05'),
(15, 'tr-0007', 'prod-10', 2, 110000, '2022-04-08 01:03:17', '2022-05-07 18:04:10'),
(17, 'tr-0008', 'prod-10', 2, 110000, '2022-03-08 01:03:17', '2022-05-07 18:04:10');
############
ALTER TABLE `ms_produk`
  ADD PRIMARY KEY (`no_urut`),
  ADD UNIQUE KEY `kode_produk` (`kode_produk`),
  ADD KEY `nama_produk` (`nama_produk`);
ALTER TABLE `tr_penjualan`
  ADD PRIMARY KEY (`id`),
  ADD UNIQUE KEY `kode_transaksi` (`kode_transaksi`);
ALTER TABLE `tr_penjualan_detail`
  ADD PRIMARY KEY (`id`),
  ADD KEY `kode_transaksi` (`kode_transaksi`),
  ADD KEY `kode_produk` (`kode_produk`);
ALTER TABLE `ms_produk`
  MODIFY `no_urut` int(11) NOT NULL AUTO_INCREMENT, AUTO_INCREMENT=111;
ALTER TABLE `tr_penjualan`
  MODIFY `id` int(11) NOT NULL AUTO_INCREMENT, AUTO_INCREMENT=116;
ALTER TABLE `tr_penjualan_detail`
  MODIFY `id` int(11) NOT NULL AUTO_INCREMENT, AUTO_INCREMENT=114;


###########################3
#jawaban dari soal
SELECT
date_format(pd.created_at, "%m/%Y") bln,
p.kode_produk, p.nama_produk,
count(*) `total trans`, sum(pd.qty) `total item`,
min(pd.created_at) `tgl min`, max(pd.created_at) `tgl max`
FROM `ms_produk` p
inner join tr_penjualan_detail pd on p.kode_produk = pd.kode_produk
group by p.kode_produk, p.nama_produk, bln
order by bln asc, p.kode_produk asc;
##
explain
SELECT
date_format(pd.created_at, "%m/%Y") bln,
p.kode_produk, p.nama_produk, count(*) `total`, sum(pd.qty) `total item`,
min(pd.created_at) `tgl min`, max(pd.created_at) `tgl max`
FROM `ms_produk` p
inner join tr_penjualan_detail pd on p.kode_produk = pd.kode_produk
group by p.kode_produk, p.nama_produk, bln
order by bln asc, p.kode_produk asc;


#jawaban seharusnya. Fyi tanggal adanya di transaksi utama bukan detail
SELECT
tblBulan.bln,
p.kode_produk, p.nama_produk, count(*) `total trans`, sum(pd.qty) `jmlh barang`,
min(pjual.created_at) `tgl min`, max(pjual.created_at) `tgl max`,
group_concat(pjual.kode_transaksi) `all trans`
FROM
(
   SELECT date_format(created_at, "%m/%Y") bln FROM `tr_penjualan` group by bln
) tblBulan
left join tr_penjualan pjual on tblBulan.bln = date_format(pjual.created_at,  "%m/%Y")
inner join tr_penjualan_detail pd on pjual.kode_transaksi = pd.kode_transaksi
join `ms_produk` p on p.kode_produk = pd.kode_produk
group by
   p.kode_produk, p.nama_produk, tblBulan.bln
order by
    bln asc, p.kode_produk asc;
###
explain
SELECT
tblBulan.bln,
p.kode_produk, p.nama_produk, count(*) `total`, sum(pd.qty) `jmlh barang`,
min(pd.created_at) `tgl min`, max(pd.created_at) `tgl max`
FROM
(
   SELECT date_format(created_at, "%m/%Y") bln FROM `tr_penjualan_detail` group by bln
) tblBulan
left join tr_penjualan pjual on tblBulan.bln = date_format(pjual.created_at,  "%m/%Y")
inner join tr_penjualan_detail pd on pjual.kode_transaksi = pd.kode_transaksi
join `ms_produk` p on p.kode_produk = pd.kode_produk

group by p.kode_produk, p.nama_produk, tblBulan.bln
order by bln asc, p.kode_produk asc;


#tahun
explain
SELECT date_format(created_at, "%m/%Y") bln FROM `tr_penjualan_detail` group by bln;

#product
explain
SELECT
p.kode_produk, p.nama_produk, date_format(pd.created_at, "%m/%Y") bln
FROM `ms_produk` p
inner join tr_penjualan_detail pd on p.kode_produk = pd.kode_produk;
############

explain
SELECT count(*) c,
p.kode_produk, p.nama_produk, date_format(pd.created_at, "%m/%Y") bln
FROM `ms_produk` p
inner join tr_penjualan_detail pd on p.kode_produk = pd.kode_produk
group by p.kode_produk, p.nama_produk, bln
order by p.kode_produk;
#########

explain
SELECT
kode_transaksi, min(created_at) dt,count(*) prod
FROM `tr_penjualan_detail` group by kode_transaksi;