SQLize Online / PHPize Online  /  SQLtest Online

A A A
Share      Blog   Popular
Copy Format Clear
CREATE TABLE NAMES ( id integer , name varchar ); INSERT INTO NAMES (id, name) VALUES('4969', 'Миг таблетки п/о 400мг упаковка №10'); CREATE TABLE FULLNAMES ( id integer, idName integer, producer varchar (256) ); INSERT INTO FULLNAMES (id, idName, producer) VALUES('19288', '4969', 'Berlin-Chemie AG Германия'); CREATE TABLE SERIES ( id integer , idFullName integer, dtCame timestamp without time zone, numCame float, numRest float, numRestOnStorage float, priceRO float ); INSERT INTO SERIES (id, idFullName, dtCame, numCame, numRest, numRestOnStorage, priceRO) VALUES('65773', '19288', '2022-04-12 14:12:12', '3.0', '0.0', '0.0', '3.7'); INSERT INTO SERIES (id, idFullName, dtCame, numCame, numRest, numRestOnStorage, priceRO) VALUES('67664', '19288','2022-04-25 19:03:55', '2.0', '0.0', '0.0', '3.7'); INSERT INTO SERIES (id, idFullName, dtCame, numCame, numRest, numRestOnStorage, priceRO) VALUES('68454', '19288','2022-05-27 18:07:06', '5.0', '0.0', '0.0', '3.17'); CREATE TABLE SALES ( id integer, dtSale timestamp without time zone, idSerie integer, numSold float ); INSERT INTO SALES (id, dtSale, idSerie, numSold) VALUES('355583', '2022-04-18 18:04:22', '65773','1.0'); INSERT INTO SALES (id, dtSale, idSerie, numSold) VALUES('357799', '2022-04-25 19:03:55', '65773','2.0'); INSERT INTO SALES (id, dtSale, idSerie, numSold) VALUES('359967', '2022-05-19 13:27:11', '67664', '1.0'); INSERT INTO SALES (id, dtSale, idSerie, numSold) VALUES('360096', '2022-05-20 14:21:28', '67664','1.0'); INSERT INTO SALES (id, dtSale, idSerie, numSold) VALUES('360961', '2022-06-01 17:24:06', '68454', '1.0'); INSERT INTO SALES (id, dtSale, idSerie, numSold) VALUES('362298', '2022-06-15 17:14:10', '68454', '1.0'); INSERT INTO SALES (id, dtSale, idSerie, numSold) VALUES('362877', '2022-06-22 10:57:29', '68454','1.0'); INSERT INTO SALES (id, dtSale, idSerie, numSold) VALUES('363006', '2022-06-23 13:46:56', '68454', '1.0'); INSERT INTO SALES (id, dtSale, idSerie, numSold) VALUES('363025', '2022-06-23 14:52:36', '68454', '1.0'); select id, total_sold, numCame, lead(dtcame) over (partition by idfullname order by id) - last_sold_at from SERIES join ( select idserie, sum(numSold) as total_sold, max(dtsale) as last_sold_at from SALES group by idserie ) sales_info on SERIES.id = sales_info.idserie;
Stuck with a problem? Got Error? Ask ChatGPT!
Copy Clear