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;
Telegram: DB developers channel