-- create
CREATE TABLE amount (
  product_id INTEGER PRIMARY KEY,
  title TEXT NOT NULL,
  shope_name TEXT NOT NULL,
  category TEXT NOT NULL,
  brand_name TEXT NOT NULL,
  dt DATE NOT NULL,
  sales DOUBLE NOT NULL,
  volume DOUBLE NOT NULL
);

-- insert
INSERT INTO amount VALUES (3456789,  'AHC玻尿酸美白补水面膜 5片',  'AHC官方旗舰店',  '美容护肤',  'AHC',  '2020-01-03', 3000, 500);
INSERT INTO amount VALUES (3456790,  '亮白补水面膜 5片',  'ACC官方店',  '美容护肤',  'ACC',  '2020-01-03', 131078, 2269);
INSERT INTO amount VALUES (3456791,  '嫩白补水面膜 5片',  'BBC官方店',  '美容护肤',  'BBC',  '2020-01-03', 73452, 4902);
INSERT INTO amount VALUES (3456792,  '口红紫色',  'STAR官方店',  '美容护肤',  'STAR',  '2020-01-03', 316482, 1491);
INSERT INTO amount VALUES (3456793,  'AHC玻尿酸美白补水面膜 5片',  'COW官方店',  '美容护肤',  'COW',  '2020-01-03', 496527, 9169);
INSERT INTO amount VALUES (3456794,  'AHC玻尿酸美白补水面膜 5片',  'MAPE官方店',  '美容护肤',  'MAPE',  '2020-01-03', 259995, 1470);
INSERT INTO amount VALUES (3456795,  'AHC玻尿酸美白补水面膜 5片',  'BAPE官方店',  '美容护肤',  'AHC',  '2020-01-03', 280072, 8369);
INSERT INTO amount VALUES (3456796,  'AHC玻尿酸美白补水面膜 5片',  'UNIQ官方店',  '美容护肤',  'UNIQ',  '2020-01-03', 291918, 1483);
INSERT INTO amount VALUES (3456797,  'AHC玻尿酸美白补水面膜 5片',  'HUAWEI官方店',  '美容护肤',  'HUAWEI',  '2020-01-03', 173296, 4492);
INSERT INTO amount VALUES (3456798,  'MBP',  'APPLE官方店',  '电子产品',  'APPLE',  '2020-01-03', 457730, 5015);
INSERT INTO amount VALUES (3456799,  'AHC玻尿酸美白补水面膜 5片',  'NOKIA官方店',  '美容护肤',  'NOKIA',  '2020-01-03', 34201, 879);
INSERT INTO amount VALUES (3456800,  '折叠屏手机',  'ZTE官方店',  '电子产品',  'ZTE',  '2020-01-03', 238955, 3880);
INSERT INTO amount VALUES (3456801,  '小米12PRO',  'MI官方店',  '电子产品',  'MI',  '2020-01-03', 183709, 9923);
INSERT INTO amount VALUES (3456802,  'AHC玻尿酸美白补水面膜 5片',  'AOC官方店',  '美容护肤',  'AOC',  '2020-01-03', 153138, 8935);
INSERT INTO amount VALUES (3456803,  'AHC玻尿酸美白补水面膜 5片',  'DELL官方店',  '美容护肤',  'DELL',  '2020-01-03', 152740, 4388);
INSERT INTO amount VALUES (3456804,  'AHC玻尿酸美白补水面膜 5片',  'PY官方店',  '美容护肤',  'AHC',  '2020-02-03', 66850, 449);
INSERT INTO amount VALUES (3456805,  'AHC玻尿酸美白补水面膜 5片',  'JAVA官方店',  '美容护肤',  'JAVA',  '2020-01-03', 218465, 1747);
INSERT INTO amount VALUES (3456806,  'AHC玻尿酸美白补水面膜 5片',  'BTC官方店',  '美容护肤',  'BTC',  '2020-01-03', 281639, 2124);
INSERT INTO amount VALUES (3456807,  'AHC玻尿酸美白补水面膜 5片',  'ASHARE官方店',  '美容护肤',  'ASHARE',  '2020-01-03', 277624, 1506);
INSERT INTO amount VALUES (3456808,  'AHC玻尿酸美白补水面膜 5片',  'AMC官方店',  '美容护肤',  'AMC',  '2020-01-03', 458262, 2460);
INSERT INTO amount VALUES (3456809,  'AHC玻尿酸美白补水面膜 5片',  'BRIT官方店',  '美容护肤',  'BRIT',  '2020-01-03', 494119, 5110);
INSERT INTO amount VALUES (3456810,  'AHC玻尿酸美白补水面膜 5片',  'JAPAN官方店',  '美容护肤',  'JAPAN',  '2020-01-03', 417250, 8857);
INSERT INTO amount VALUES (3456811,  'AHC玻尿酸美白补水面膜 5片',  'ZOOM官方店',  '美容护肤',  'ZOOM',  '2020-01-03', 129258, 5168);
INSERT INTO amount VALUES (3456812,  'AHC玻尿酸美白补水面膜 5片',  'MICROSOFT官方店',  '美容护肤',  'MICROSOFT',  '2020-01-03', 159257, 9873);
INSERT INTO amount VALUES (3456813,  'AHC玻尿酸美白补水面膜 5片',  'ZZZ官方店',  '美容护肤',  'ZZZ',  '2020-01-03', 322966, 4144);
INSERT INTO amount VALUES (3456814,  'AHC玻尿酸美白补水面膜 5片',  'AHC官方店',  '美容护肤',  'AHC',  '2020-08-03', 157160, 5822);
INSERT INTO amount VALUES (3456815,  '亮白补水面膜 5片',  'ACC官方店',  '美容护肤',  'ACC',  '2020-08-03', 343130, 3574);
INSERT INTO amount VALUES (3456816,  '嫩白补水面膜 5片',  'BBC官方店',  '美容护肤',  'BBC',  '2020-08-03', 131154, 8113);
INSERT INTO amount VALUES (3456817,  '口红紫色',  'STAR官方店',  '美容护肤',  'STAR',  '2020-08-03', 173040, 1932);
INSERT INTO amount VALUES (3456818,  'AHC玻尿酸美白补水面膜 5片',  'COW官方店',  '美容护肤',  'COW',  '2020-08-03', 170649, 3735);
INSERT INTO amount VALUES (3456819,  'AHC玻尿酸美白补水面膜 5片',  'MAPE官方店',  '美容护肤',  'MAPE',  '2020-04-03', 213197, 9389);
INSERT INTO amount VALUES (3456820,  'AHC玻尿酸美白补水面膜 5片',  'BAPE官方店',  '美容护肤',  'BAPE',  '2020-08-03', 136234, 2093);
INSERT INTO amount VALUES (3456821,  'AHC玻尿酸美白补水面膜 5片',  'UNIQ官方店',  '美容护肤',  'UNIQ',  '2020-08-03', 49129, 8281);
INSERT INTO amount VALUES (3456822,  'AHC玻尿酸美白补水面膜 5片',  'HUAWEI官方店',  '美容护肤',  'HUAWEI',  '2020-08-03', 483565, 8730);
INSERT INTO amount VALUES (3456823,  'MBP',  'APPLE官方店',  '电子产品',  'APPLE',  '2020-08-03', 377698, 8810);
INSERT INTO amount VALUES (3456824,  'AHC玻尿酸美白补水面膜 5片',  'NOKIA官方店',  '美容护肤',  'NOKIA',  '2020-08-03', 224080, 1051);
INSERT INTO amount VALUES (3456825,  '折叠屏手机',  'ZTE官方店',  '电子产品',  'ZTE',  '2020-08-03', 21906, 5058);
INSERT INTO amount VALUES (3456826,  '小米12PRO',  'MI官方店',  '电子产品',  'MI',  '2020-07-03', 233615, 4036);
INSERT INTO amount VALUES (3456827,  'AHC玻尿酸美白补水面膜 5片',  'AOC官方店',  '美容护肤',  'AOC',  '2020-08-03', 340293, 1495);
INSERT INTO amount VALUES (3456828,  'AHC玻尿酸美白补水面膜 5片',  'DELL官方店',  '美容护肤',  'DELL',  '2020-08-03', 339130, 7365);
INSERT INTO amount VALUES (3456829,  'AHC玻尿酸美白补水面膜 5片',  'PY官方店',  '美容护肤',  'PY',  '2020-08-03', 116587, 5271);
INSERT INTO amount VALUES (3456830,  'AHC玻尿酸美白补水面膜 5片',  'JAVA官方店',  '美容护肤',  'JAVA',  '2020-08-03', 333720, 1548);
INSERT INTO amount VALUES (3456831,  'AHC玻尿酸美白补水面膜 5片',  'BTC官方店',  '美容护肤',  'BTC',  '2020-08-03', 363951, 8425);
INSERT INTO amount VALUES (3456832,  'AHC玻尿酸美白补水面膜 5片',  'ASHARE官方店',  '美容护肤',  'ASHARE',  '2020-08-03', 147259, 8095);
INSERT INTO amount VALUES (3456833,  'AHC玻尿酸美白补水面膜 5片',  'AMC官方店',  '美容护肤',  'AMC',  '2020-08-03', 78154, 1498);
INSERT INTO amount VALUES (3456834,  'AHC玻尿酸美白补水面膜 5片',  'BRIT官方店',  '美容护肤',  'BRIT',  '2020-08-03', 239695, 7984);
INSERT INTO amount VALUES (3456835,  'AHC玻尿酸美白补水面膜 5片',  'JAPAN官方店',  '美容护肤',  'JAPAN',  '2020-08-03', 283001, 5589);
INSERT INTO amount VALUES (3456836,  'AHC玻尿酸美白补水面膜 5片',  'ZOOM官方店',  '美容护肤',  'ZOOM',  '2020-08-03', 146989, 647);
INSERT INTO amount VALUES (3456837,  'AHC玻尿酸美白补水面膜 5片',  'MICROSOFT官方店',  '美容护肤',  'MICROSOFT',  '2020-08-03', 220534, 7067);
INSERT INTO amount VALUES (3456838,  'AHC玻尿酸美白补水面膜 5片',  'ZZZ官方店',  '美容护肤',  'ZZZ',  '2020-08-03', 5941, 8539);



-- fetch 
SELECT a.month, a.brand_name, a.tot_sales FROM  
(SELECT MONTH(dt) AS month, brand_name, SUM(sales) AS tot_sales  FROM amount 
WHERE category = '美容护肤' AND dt BETWEEN '2020-01-01' AND '2020-06-30'
GROUP BY brand_name, MONTH(dt)) AS a
WHERE (SELECT COUNT(DISTINCT(b.brand_name)) FROM 
(SELECT MONTH(dt) AS month, brand_name, SUM(sales) AS tot_sales  FROM amount 
WHERE category = '美容护肤' AND dt BETWEEN '2020-01-01' AND '2020-06-30'
GROUP BY brand_name, MONTH(dt)) AS b WHERE a.month = b.month AND a.brand_name = b.brand_name and a.tot_sales > b.tot_sales)) < 3
;