Dataset to practice Windows functions in MySQL

As discussed in the previous post, MySQL is supporting “Window functions” beginning with version 8.0. The window functions enable you to tackle query problems in new, easier, and faster ways. The following are important considerations to remember.

The following is an example dataset for practice.

CREATE TABLE tbProducts(
ProductID INT, 
ProductCategory VARCHAR(200), 
Manufacturer VARCHAR(200), 
ProductName VARCHAR(200), 
ProductDescription VARCHAR(200), 
PricePerItem DECIMAL(10,2),
UnitSales INT);

(1001, 'Desktop', 'Dell', 'Dell OptiPlex 3080', 'Core i5 10500 3.1 GHz - 8 GB - SSD 512', 1658, 100),
(1002, 'Desktop', 'Dell', 'Dell Optiplex Intel Core i7-3770','16GB DDR3-512GB',450,88),
(1003, 'Desktop', 'Dell', 'Dell Desktop PC Tower 7080', '10th Gen Intel Core i7-10700, 32GB DDR4, 1TB Hdd+',2765,78),
(1004, 'Desktop', 'HP', 'HP Elite 8300 SFF Quad Core i7-3770', '3.4GHz 16GB 256GB SSD + 1000GB HDD DVD WiFi Windows',394, 99),
(1005, 'Desktop', 'HP', 'HP EliteDesk 800 G1 SFF Black Desktop PC Intel Quad Core i5-4570', '3.20GHz, 8GB RAM, 256GB SSD',323, 110),
(1006, 'Desktop', 'HP', 'HP ProDesk 600 G1 ','SFF Slim Business Desktop Computer, Intel i5-4570 up to 3.60 GHz, 8GB RAM',316,230),
(1007, 'Desktop', 'Dell', 'Dell OptiPlex 3020 SFF 4th Gen Core i5-4590', '8GB 240GB SSD DVDRW',338, 120),
(1008, 'Desktop', 'Lenovo', 'Lenovo ThinkCentre High Performance M73 SFF Desktop Computer','Intel Core i3 Processor 3.4GHz 8GB RAM 512GB SSD DVD',1899, 230),
(1009, 'Desktop', 'HP', 'HP Compaq Prodesk 6200 Pro Slim Business Desktop Computer','Small Form Factor (SFF), Intel i5-2400 up to 3.4GHz, 8GB DDR3',398, 200),
(1010, 'Desktop', 'Dell', 'Dell Inspiron 3910 Desktop (2022)', 'Core i5-512GB SSD - 8GB RAM | 6 Cores @ 4.4 GHz - 12th Gen CPU Win 11 Home',1907,100),
(1011, 'Desktop', 'Lenovo', 'Lenovo Desktop M91p SFF Core i7-2600','3.40GHz 8GB 500GB HDD Win 10 Pro (Renewed)',490, 123),
(1012, 'Laptop', 'Dell', 'Dell Latitude 7480 Laptop Pc','14 Fhd (1920X1080) Non-Touch, Intel I5 2.60Ghz Processor, 16 Gb Ram Ddr4, 512 Gb Nvme',702, 230),
(1013, 'Laptop', 'HP', '2022 HP 15.6 FHD Laptop Computer', '11th Gen Intel Core i5 1135G7 Beats Intel i7 1065G7, 16GB RAM, 1TB SSD, Intel Iris X',1875, 320),
(1014, 'Laptop', 'Lenovo', 'Lenovo 2022 Newest IdeaPad 14" FHD IPS Laptop Computer', 'Intel Core i5-10210U, Quad Core Upto 4.2 GHz, 12GB RAM, 512GB PCIe',1645, 100),
(1015, 'Laptop', 'HP', 'HP 2022 Newest HP 15.6in FHD 1080P IPS Display Laptop Computer', '11th Gen Intel Quad-Core i5-1135G7 Upto 4.2GHz',1905, 200),
(1016, 'Laptop', 'HP','HP 15 Business Laptop Computer', '11th Gen Intel Core i5-1135G7, 15.6" FHD IPS Display, Windows 11 Pro, 16GB RAM, 512GB SSD,',1900, 120),
(1017, 'Laptop', 'Acer', 'Acer Nitro 5 AN515 Gaming Notebook', '11th Gen Intel Core i9-119000H Octa Core 2.50GHz Upto 4.90GHz/16GB',4299, 145),
(1018, 'Laptop', 'Acer', 'Acer Predator Helios 300 PH315-55-70ZV Laptop Computer (2022)', 'Intel i7-12700H | NVIDIA GeForce RTX 3060 GPU | 15.6"',5013, 10),
(1019, 'Laptop', 'Acer', 'Acer Aspire 5 15.6" FHD 1080P Laptop Computer', 'AMD Ryzen 3 3200U (Beat i5-7200u) 8GB RAM 256GB SSD',2099, 10),
(1020, 'Laptop', 'Acer', 'Acer Aspire 3 Slim Laptop Computer', '15.6 inches Full HD Display, Intel Quad-Core i5-1035G1 (Up to 3.6 GHz), 12GB',2058, 18);

DISTINCT ProductCategory, Manufacturer,
SUM(PricePerItem * UnitSales) OVER(PARTITION BY Manufacturer, ProductCategory) AS Manufacturer_TotalSales,
AVG(PricePerItem * UnitSales) OVER(PARTITION BY Manufacturer, ProductCategory) AS Manufacturer_AVGSales,
MAX(UnitSales) OVER(PARTITION BY Manufacturer, ProductCategory) 
AS HighestSalesPerCategry,
MIN(UnitSales) OVER(PARTITION BY Manufacturer, ProductCategory) 
AS LowestSalesPerCategry	
FROM tbProducts
ORDER BY Manufacturer;

Output is -
Laptop	Acer	731519.00	182879.750000	145	10
Desktop	Dell	652330.00	130466.000000	120	78
Laptop	Dell	161460.00	161460.000000	230	230
Desktop	HP	226816.00	56704.000000	230	99
Laptop	HP	1209000.00	403000.000000	320	120
Desktop	Lenovo	497040.00	248520.000000	230	123
Laptop	Lenovo	164500.00	164500.000000	100	100

One comment

Leave a Reply

Fill in your details below or click an icon to log in: Logo

You are commenting using your account. Log Out /  Change )

Facebook photo

You are commenting using your Facebook account. Log Out /  Change )

Connecting to %s