SQLize Online / PHPize Online  /  SQLtest Online

CREATE EXTENSION IF NOT EXISTS "uuid-ossp"; create table temp_bets ( id uuid NOT NULL DEFAULT uuid_generate_v4(), amount numeric(19,2) NOT NULL DEFAULT '0', "createdAt" TIMESTAMP NOT NULL DEFAULT now(), "userId" character varying NOT NULL ); create table temp_deposits ( "transactionId" varchar not null primary key, "userId" character varying NOT NULL, amount numeric(19,2) default '0'::numeric, "activatedDate" timestamptz, "prevActivatedDate" timestamptz, "prevNoCommissionDate" timestamptz, "sumCommissionBet" numeric(19,2) default '0', "sumCommissionDeposit" numeric(19,2) not null default '0', "sumBetTransaction" numeric(19,2) default '0' ); CREATE INDEX idx_prevNoCommissionDate ON temp_deposits ("prevNoCommissionDate"); CREATE INDEX idx_activatedDate ON temp_deposits ("activatedDate"); CREATE INDEX idx_sumBetTransaction ON temp_deposits ("sumBetTransaction"); CREATE INDEX idx_amount ON temp_deposits ("amount"); CREATE INDEX temp_deposits_userId_idx ON temp_deposits ("userId"); insert into temp_bets(id, "userId", amount, "createdAt") values ('a9794330-704e-40a2-8e60-aa19045cee1d', '858930', 1.00, '2023-04-09 00:44:43.947969'), ('d2c6e9f2-241c-44c2-b872-ef4499f68144', '858930', 15.00, '2023-04-10 00:44:44.012138'), ('d2c6e9f2-241c-44c2-b872-ef4499f68145', '858930', 1211.00, '2023-04-11 00:44:44.012138'), ('d2c6e9f2-241c-44c2-b872-ef4499f68146', '858930', 17.00, '2023-04-12 00:44:44.012138'), ('d2c6e9f2-241c-44c2-b872-ef4499f68147', '858930', 12.00, '2023-04-13 00:44:44.012138'), ('d2c6e9f2-241c-44c2-b872-ef4499f68148', '858930', 15.00, '2023-04-14 00:44:44.012138'), ('d2c6e9f2-241c-44c2-b872-ef4499f68149', '858930', 519.00, '2023-04-14 00:45:44.012138'), ('d2c6e9f2-241c-44c2-b872-ef4499f68150', '858930', 15.00, '2023-04-16 00:44:44.012138'); insert into temp_deposits("transactionId", "userId", amount, "activatedDate") values ('66697010', '858930', 20.00, '2023-04-08 23:25:48.067+00'), ('66697011', '858930', 20.00, '2023-04-09 23:25:48.067+00'), ('66697012', '858930', 20.00, '2023-04-10 23:25:48.067+00'), ('66697013', '858930', 20.00, '2023-04-11 23:25:48.067+00'), ('66697014', '858930', 20.00, '2023-04-12 23:25:48.067+00'), ('66697015', '858930', 20.00, '2023-04-13 23:25:48.067+00'), ('66697016', '858930', 20.00, '2023-04-14 23:25:48.067+00'), ('66697017', '858930', 20.00, '2023-04-14 23:26:48.067+00'); -- обновляем предыдущие даты UPDATE temp_deposits td SET "prevActivatedDate" = subquery."prevActivatedDate" FROM ( SELECT td2."transactionId", LAG(td2."activatedDate") OVER (PARTITION BY td2."userId" ORDER BY td2."activatedDate") AS "prevActivatedDate" FROM temp_deposits td2 LEFT JOIN temp_deposits td1 ON td2."userId" = td1."userId" WHERE td2."userId" = td1."userId" ) AS subquery WHERE td."transactionId" = subquery."transactionId"; -- переносим данные о ставках суммируя UPDATE temp_deposits td SET "sumBetTransaction" = COALESCE(subquery."sumBetTransaction", 0) FROM ( SELECT td."transactionId", SUM(tb.amount) AS "sumBetTransaction" FROM temp_deposits td LEFT JOIN temp_bets tb ON tb."userId" = td."userId" AND tb."createdAt" >= td."prevActivatedDate" AND tb."createdAt" < td."activatedDate" GROUP BY td."transactionId" ) AS subquery WHERE td."transactionId" = subquery."transactionId"; -- обновляем дату отыгрыша (фиксить, что-то не правильное) UPDATE temp_deposits td1 SET "prevNoCommissionDate" = td1."activatedDate" WHERE EXISTS ( SELECT 1 FROM ( SELECT SUM(td2."sumBetTransaction") as total_bets_amount, SUM(td2.amount) as total_deposits_amount FROM temp_deposits td2 WHERE td2."activatedDate" >= COALESCE(( SELECT MAX(td3."prevNoCommissionDate") FROM temp_deposits td3 WHERE td3."userId" = td2."userId" AND td3."prevNoCommissionDate" IS NOT NULL), '1970-01-01'::timestamp) AND td2."activatedDate" < td1."activatedDate" AND td2."userId" = td1."userId" AND td1."sumBetTransaction" <> 0 ) td_totals WHERE COALESCE(td_totals.total_bets_amount / NULLIF(td_totals.total_deposits_amount, 0), 0) >= 3 AND td_totals.total_bets_amount IS NOT NULL ) AND td1."sumBetTransaction" <> 0; -- обновить sumCommissionBet (фиксить, что-то не правильное) -- 1 вариант для проверки логики, который не годится для больших данных UPDATE temp_deposits td SET "sumCommissionBet" = ( SELECT COALESCE(SUM(td2."sumBetTransaction"), 0) FROM temp_deposits td2 WHERE td2."userId" = td."userId" AND td2."activatedDate" >= ( SELECT MAX(td3."prevNoCommissionDate") FROM temp_deposits td3 WHERE td3."userId" = td."userId" AND td3."activatedDate" < td."activatedDate" ) AND td2."activatedDate" < td."activatedDate" ); -- 2 вариант через группировку с join как при подтягивании ставок, внутри with with cte as ( SELECT td."transactionId", SUM(td2."sumBetTransaction") AS "sumBetTransaction" FROM temp_deposits td LEFT JOIN temp_deposits td2 ON td2."userId" = td."userId" AND td2."activatedDate" >= ( SELECT MAX(td3."prevNoCommissionDate") FROM temp_deposits td3 WHERE td3."userId" = td2."userId" AND td3."activatedDate" < td2."activatedDate" AND td3."prevNoCommissionDate" IS NOT NULL ) AND td2."activatedDate" < td."activatedDate" GROUP BY td."transactionId" ) select * from cte; -- обновить sumCommissionDeposit select * from temp_deposits order by "activatedDate" desc;