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) 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";
-- обновляем дату отыгрыша (тут проблема что обновленные значения prevNoCommissionDate не видны в подзапросе и суммирует с 1970-01-01 всегда)
-- 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 td2."activatedDate" >= COALESCE(td1."prevNoCommissionDate", '1970-01-01'::timestamp)
-- -- 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
-- );
-- 2 вариант
-- WITH RECURSIVE temp_deposits_rec AS (
-- SELECT
-- td."transactionId",
-- td."userId",
-- td."activatedDate",
-- td."prevNoCommissionDate",
-- td."sumBetTransaction",
-- td."amount",
-- 0 AS "iteration"
-- FROM temp_deposits td
-- WHERE td."prevNoCommissionDate" IS NULL
-- UNION ALL
-- SELECT
-- td."transactionId",
-- td."userId",
-- td."activatedDate",
-- COALESCE(td_rec."prevNoCommissionDate", td."activatedDate") AS "prevNoCommissionDate",
-- td."sumBetTransaction",
-- td."amount",
-- td_rec."iteration" + 1 AS "iteration"
-- FROM temp_deposits td
-- JOIN temp_deposits_rec td_rec ON
-- td."userId" = td_rec."userId"
-- AND td."activatedDate" > td_rec."activatedDate"
-- AND td."prevNoCommissionDate" IS NULL
-- WHERE
-- COALESCE((
-- SELECT SUM(td2."sumBetTransaction") as total_bets_amount
-- FROM temp_deposits td2
-- WHERE
-- td2."userId" = td."userId"
-- AND td2."activatedDate" >= COALESCE(td_rec."prevNoCommissionDate", '1970-01-01'::timestamp)
-- AND td2."activatedDate" < td."activatedDate"
-- ), 0) / NULLIF(td."amount", 0) >= 3
-- )
-- UPDATE temp_deposits td
-- SET "prevNoCommissionDate" = td_rec."prevNoCommissionDate"
-- FROM temp_deposits_rec td_rec
-- WHERE td."transactionId" = td_rec."transactionId"
-- AND td_rec."iteration" = (
-- SELECT MAX(td_rec_inner."iteration")
-- FROM temp_deposits_rec td_rec_inner
-- WHERE td_rec_inner."userId" = td."userId"
-- );
-- 3 вариант
WITH RECURSIVE temp_deposits_rec AS (
SELECT
td."userId",
td."activatedDate",
td."prevNoCommissionDate",
td."sumBetTransaction",
td."amount",
td."activatedDate" AS "id",
0 AS "iteration"
FROM temp_deposits td
WHERE td."prevNoCommissionDate" IS NULL
UNION ALL
SELECT
td."userId",
td."activatedDate",
COALESCE(td_rec."prevNoCommissionDate", td."activatedDate") AS "prevNoCommissionDate",
td."sumBetTransaction",
td."amount",
td."activatedDate" AS "id",
td_rec."iteration" + 1 AS "iteration"
FROM temp_deposits td
JOIN temp_deposits_rec td_rec ON
td."userId" = td_rec."userId"
AND td."activatedDate" > td_rec."activatedDate"
AND td."prevNoCommissionDate" IS NULL
WHERE
COALESCE((
SELECT SUM(td2."sumBetTransaction") as total_bets_amount
FROM temp_deposits td2
WHERE
td2."userId" = td."userId"
AND td2."activatedDate" >= COALESCE(td_rec."prevNoCommissionDate", '1970-01-01'::timestamp)
AND td2."activatedDate" < td."activatedDate"
), 0) / NULLIF(td."amount", 0) >= 3
)
UPDATE temp_deposits td
SET "prevNoCommissionDate" = td_rec."prevNoCommissionDate"
FROM temp_deposits_rec td_rec
WHERE td."userId" = td_rec."userId"
AND td."activatedDate" = td_rec."id"
AND td_rec."iteration" = (
SELECT MAX(td_rec_inner."iteration")
FROM temp_deposits_rec td_rec_inner
WHERE td_rec_inner."userId" = td."userId"
);
-- обновить 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(COALESCE(td3."prevNoCommissionDate", '1970-01-01'::timestamp))
FROM temp_deposits td3
WHERE td3."userId" = td2."userId"
AND td3."activatedDate" < td."activatedDate"
-- AND td3."prevNoCommissionDate" IS NOT NULL
)
AND td2."activatedDate" < td."activatedDate"
GROUP BY td."transactionId"
)
UPDATE temp_deposits tds
SET "sumCommissionBet" = cte."sumBetTransaction"
FROM cte
WHERE tds."transactionId" = cte."transactionId";
-- обновить sumCommissionDeposit
with cte as (
SELECT
td."transactionId",
SUM(td2."amount") AS "sumDepositTransaction"
FROM temp_deposits td
LEFT JOIN temp_deposits td2
ON td2."userId" = td."userId"
AND td2."activatedDate" >= (
SELECT MAX(COALESCE(td3."prevNoCommissionDate", '1970-01-01'::timestamp))
FROM temp_deposits td3
WHERE td3."userId" = td2."userId"
AND td3."activatedDate" = td."activatedDate"
-- AND td3."prevNoCommissionDate" IS NOT NULL
)
AND td2."activatedDate" < td."activatedDate"
GROUP BY td."transactionId"
)
UPDATE temp_deposits tds
SET "sumCommissionDeposit" = cte."sumDepositTransaction"
FROM cte
WHERE tds."transactionId" = cte."transactionId";
select * from temp_deposits order by "activatedDate" desc;
Telegram: DB developers channel