yuriandClaude Opus 5.5
9749da5ae7
Co-Authored-By: Claude Opus 5.5 (1M context) <noreply@anthropic.com>
53 lines
3.2 KiB
SQL
53 lines
3.2 KiB
SQL
-- Раскладывает историю демо-компаний («Демо: …») по последним трём неделям, чтобы аналитика и
|
|
-- история карточек выглядели как у работающего агентства. Запускать сразу после seed-demo.mjs.
|
|
|
|
-- Объекты в работе: пришли от 1 до 20 дней назад; объекты в пуле — за последние трое суток.
|
|
WITH d AS (
|
|
SELECT l.id,
|
|
EXISTS (SELECT 1 FROM events e WHERE e.lead_id = l.id AND e.type <> 'created') AS worked
|
|
FROM leads l JOIN companies c ON c.id = l.company_id WHERE c.name LIKE 'Демо:%'
|
|
)
|
|
UPDATE leads SET created_at = CASE
|
|
WHEN d.worked THEN now() - make_interval(days => 1 + (leads.id * 7 % 20), hours => leads.id * 5 % 9)
|
|
ELSE now() - make_interval(hours => leads.id * 13 % 70, mins => leads.id * 17 % 60) END
|
|
FROM d WHERE leads.id = d.id;
|
|
|
|
-- События объекта: по порядку между его приходом и «час назад».
|
|
WITH e AS (
|
|
SELECT ev.id, l.created_at,
|
|
row_number() OVER (PARTITION BY ev.lead_id ORDER BY ev.id) - 1 AS n,
|
|
count(*) OVER (PARTITION BY ev.lead_id) AS cnt
|
|
FROM events ev JOIN leads l ON l.id = ev.lead_id JOIN companies c ON c.id = l.company_id
|
|
WHERE c.name LIKE 'Демо:%'
|
|
)
|
|
UPDATE events SET at = e.created_at + (e.n::float / e.cnt) * (now() - interval '1 hour' - e.created_at)
|
|
FROM e WHERE events.id = e.id;
|
|
|
|
-- Клиенты заведены 2–16 дней назад; их события — так же по порядку.
|
|
UPDATE clients SET created_at = now() - make_interval(days => 2 + (clients.id * 5 % 15), hours => clients.id * 3 % 8)
|
|
FROM companies c WHERE c.id = clients.company_id AND c.name LIKE 'Демо:%';
|
|
|
|
WITH e AS (
|
|
SELECT ev.id, cl.created_at,
|
|
row_number() OVER (PARTITION BY ev.client_id ORDER BY ev.id) - 1 AS n,
|
|
count(*) OVER (PARTITION BY ev.client_id) AS cnt
|
|
FROM events ev JOIN clients cl ON cl.id = ev.client_id JOIN companies c ON c.id = cl.company_id
|
|
WHERE c.name LIKE 'Демо:%' AND ev.lead_id IS NULL
|
|
)
|
|
UPDATE events SET at = e.created_at + (e.n::float / e.cnt) * (now() - interval '1 hour' - e.created_at)
|
|
FROM e WHERE events.id = e.id;
|
|
|
|
-- Запись о сделке стоит в день сделки.
|
|
UPDATE events SET at = (d.deal_date + time '14:00') AT TIME ZONE 'Asia/Yerevan'
|
|
FROM deals d JOIN companies c ON c.id = d.company_id
|
|
WHERE c.name LIKE 'Демо:%' AND events.type = 'deal' AND (events.lead_id = d.lead_id OR events.client_id = d.client_id);
|
|
|
|
-- «Взят» — по записи истории; таймер возврата в пул отсчитывается от последней смены статуса.
|
|
UPDATE leads SET taken_at = t.at
|
|
FROM (SELECT lead_id, min(at) AS at FROM events WHERE type = 'taken' GROUP BY lead_id) t, companies c
|
|
WHERE leads.id = t.lead_id AND c.id = leads.company_id AND c.name LIKE 'Демо:%';
|
|
|
|
UPDATE leads SET status_changed_at = s.at
|
|
FROM (SELECT lead_id, max(at) AS at FROM events WHERE type IN ('taken', 'status', 'deal', 'blacklisted') GROUP BY lead_id) s, companies c
|
|
WHERE leads.id = s.lead_id AND c.id = leads.company_id AND c.name LIKE 'Демо:%';
|