Run Code
|
API
|
Code Wall
|
Misc
|
Feedback
|
Login
|
Theme
|
Privacy
|
Patreon
w3c sql data
DROP TABLE IF EXISTS dxy_customers; CREATE TABLE dxy_customers ("customerid" int, "country" varchar(11)) ; INSERT INTO dxy_customers ("customerid", "country") VALUES (1, 'Germany'), (2, 'Mexico'), (3, 'Mexico'), (4, 'UK'), (5, 'Sweden'), (6, 'Germany'), (7, 'France'), (8, 'Spain'), (9, 'France'), (10, 'Canada'), (11, 'UK'), (12, 'Argentina'), (13, 'Mexico'), (14, 'Switzerland'), (15, 'Brazil'), (16, 'UK'), (17, 'Germany'), (18, 'France'), (19, 'UK'), (20, 'Austria') ; DROP TABLE IF EXISTS dxy_categories; CREATE TABLE dxy_categories ("categoryid" int, "categoryname" varchar(14)) ; INSERT INTO dxy_categories ("categoryid", "categoryname") VALUES (1, 'Beverages'), (2, 'Condiments'), (3, 'Confections'), (4, 'Dairy Products'), (5, 'Grains/Cereals'), (6, 'Meat/Poultry'), (7, 'Produce'), (8, 'Seafood') ; DROP TABLE IF EXISTS dxy_employees; CREATE TABLE dxy_employees ("employeeid" int, "birthdate" timestamp) ; INSERT INTO dxy_employees ("employeeid", "birthdate") VALUES (1, '1968-12-08'), (2, '1952-02-19'), (3, '1963-08-30'), (4, '1958-09-19'), (5, '1955-03-04') ; DROP TABLE IF EXISTS dxy_orderdetails; CREATE TABLE dxy_orderdetails ("orderid" int, "productid" int, "quantity" int) ; INSERT INTO dxy_orderdetails ("orderid", "productid", "quantity") VALUES (10248, 11, 12), (10248, 42, 10), (10248, 72, 5), (10249, 14, 9), (10249, 51, 40), (10250, 41, 10), (10250, 51, 35), (10250, 65, 15), (10251, 22, 6), (10251, 57, 15), (10251, 65, 20), (10252, 20, 40), (10252, 33, 25), (10252, 60, 40), (10253, 31, 20), (10253, 39, 42), (10253, 49, 40), (10254, 24, 15), (10254, 55, 21), (10254, 74, 21), (10255, 2, 20), (10255, 16, 35), (10255, 36, 25), (10255, 59, 30), (10256, 53, 15), (10256, 77, 12), (10257, 27, 25), (10257, 39, 6), (10257, 77, 15), (10258, 2, 50), (10258, 5, 65), (10258, 32, 6), (10259, 21, 10), (10259, 37, 1), (10260, 41, 16), (10260, 57, 50), (10260, 62, 15), (10260, 70, 21), (10261, 21, 20), (10261, 35, 20), (10262, 5, 12), (10262, 7, 15), (10262, 56, 2), (10263, 16, 60), (10263, 24, 28), (10263, 30, 60), (10263, 74, 36), (10264, 2, 35), (10264, 41, 25), (10265, 17, 30), (10265, 70, 20), (10266, 12, 12), (10267, 40, 50), (10267, 59, 70), (10267, 76, 15), (10268, 29, 10), (10268, 72, 4), (10269, 33, 60), (10269, 72, 20), (10270, 36, 30), (10270, 43, 25), (10271, 33, 24), (10272, 20, 6), (10272, 31, 40), (10272, 72, 24), (10273, 10, 24), (10273, 31, 15), (10273, 33, 20), (10273, 40, 60), (10273, 76, 33), (10274, 71, 20), (10274, 72, 7), (10275, 24, 12), (10275, 59, 6), (10276, 10, 15), (10276, 13, 10), (10277, 28, 20), (10277, 62, 12), (10278, 44, 16), (10278, 59, 15), (10278, 63, 8), (10278, 73, 25), (10279, 17, 15), (10280, 24, 12), (10280, 55, 20), (10280, 75, 30), (10281, 19, 1), (10281, 24, 6), (10281, 35, 4), (10282, 30, 6), (10282, 57, 2), (10283, 15, 20), (10283, 19, 18), (10283, 60, 35), (10283, 72, 3), (10284, 27, 15), (10284, 44, 21), (10284, 60, 20), (10284, 67, 5) ; DROP TABLE IF EXISTS dxy_orders; CREATE TABLE dxy_orders ("orderid" int, "customerid" int) ; INSERT INTO dxy_orders ("orderid", "customerid") VALUES (10248, 90), (10249, 81), (10250, 34), (10251, 84), (10252, 76), (10253, 34), (10254, 14), (10255, 68), (10256, 88), (10257, 35), (10258, 20), (10259, 13), (10260, 55), (10261, 61), (10262, 65), (10263, 20), (10264, 24), (10265, 7), (10266, 87) ; DROP TABLE IF EXISTS dxy_products; CREATE TABLE dxy_products ("productid" int, "categoryid" int, "price" int) ; INSERT INTO dxy_products ("productid", "categoryid", "price") VALUES (1, 1, 18), (2, 1, 19), (3, 2, 10), (4, 2, 22), (5, 2, 21.35), (6, 2, 25), (7, 7, 30), (8, 2, 40), (9, 6, 97), (10, 8, 31), (11, 4, 21), (12, 4, 38), (13, 8, 6), (14, 7, 23.25), (15, 2, 15.5), (16, 3, 17.45), (17, 6, 39), (18, 8, 62.5), (19, 3, 9.2), (20, 3, 81), (21, 3, 10), (22, 5, 21), (23, 5, 9), (24, 1, 4.5), (25, 3, 14), (26, 3, 31.23), (27, 3, 43.9), (28, 7, 45.6), (29, 6, 123.79), (30, 8, 25.89), (31, 4, 12.5), (32, 4, 32), (33, 4, 2.5), (34, 1, 14), (35, 1, 18), (36, 8, 19), (37, 8, 26), (38, 1, 263.5), (39, 1, 18), (40, 8, 18.4), (41, 8, 9.65), (42, 5, 14), (43, 1, 46), (44, 2, 19.45), (45, 8, 9.5), (46, 8, 12), (47, 3, 9.5), (48, 3, 12.75), (49, 3, 20) ; select categoryid, productid, round(avg(quantity))as med from (select a.categoryid, b.productid, c.quantity, row_number() over (partition by a.categoryid, b.productid order by c.quantity) as prod_row_num_asc, row_number() over (partition by a.categoryid, b.productid order by c.quantity desc) as prod_row_num_desc from dxy_categories a join dxy_products b on a.categoryid = b.categoryid join dxy_orderdetails c on b.productid = c.productid) as tp where prod_row_num_asc in (prod_row_num_desc, prod_row_num_desc-1, prod_row_num_desc+1) group by 1, 2;
run
|
edit
|
history
|
help
0
select from json[]
Ankit
3c
sss
Weekly Average Starting on Different Days
test
FlujoPadre
Many to many
Leetcode 571 Find Median Given Frequency of Numbers & 614 Second Degree Follower
loggin_trigger-audit