Поиск пабликов соцсети с высокой конверсией в первое сообщение

-- users - Пользователи

ALTER TABLE STV2025011410__STAGING.groups DROP CONSTRAINT IF EXISTS STV2025011410__STAGING_groups_admin_id_fk;
ALTER TABLE STV2025011410__STAGING.dialogs DROP CONSTRAINT IF EXISTS STV2025011410__STAGING_dialogs_message_from_fk;
ALTER TABLE STV2025011410__STAGING.dialogs DROP CONSTRAINT IF EXISTS STV2025011410__STAGING_dialogs_message_to_fk;

ALTER TABLE STV2025011410__STAGING.dialogs DROP CONSTRAINT IF EXISTS STV2025011410__STAGING_dialogs_message_id_pk;
ALTER TABLE STV2025011410__STAGING.dialogs DROP CONSTRAINT IF EXISTS STV2025011410__STAGING_dialogs_message_id_unique;

ALTER TABLE STV2025011410__STAGING.users DROP CONSTRAINT IF EXISTS STV2025011410__STAGING_users_id_pk;
ALTER TABLE STV2025011410__STAGING.users DROP CONSTRAINT IF EXISTS STV2025011410__STAGING_users_id_unique;
ALTER TABLE STV2025011410__STAGING.users DROP CONSTRAINT IF EXISTS STV2025011410__STAGING_users_age_unique;

DROP TABLE IF EXISTS STV2025011410__STAGING.users;

CREATE TABLE IF NOT EXISTS STV2025011410__STAGING.users(
    id INT NOT NULL, -- уникальный идентификатор пользователя.
    chat_name VARCHAR(200), -- имя полшьзователя/название чата.
    registration_dt DATETIME, -- дата регистрации пользователя.
    country VARCHAR(200), -- страна пользователя.
    age INT, -- возраст.
    CONSTRAINT STV2025011410__STAGING_users_id_pk PRIMARY KEY (id),
    CONSTRAINT STV2025011410__STAGING_users_id_unique UNIQUE (id),
    CONSTRAINT STV2025011410__STAGING_users_age_unique CHECK ((age > 0) AND (age < 1000))
)
ORDER BY id
SEGMENTED BY HASH(id) ALL NODES;



-- groups - Группы пользователей

ALTER TABLE STV2025011410__STAGING.groups DROP CONSTRAINT IF EXISTS STV2025011410__STAGING_groups_id_pk;
ALTER TABLE STV2025011410__STAGING.groups DROP CONSTRAINT IF EXISTS STV2025011410__STAGING_groups_id_unique;
ALTER TABLE STV2025011410__STAGING.dialogs DROP CONSTRAINT IF EXISTS STV2025011410__STAGING_dialogs_message_group_fk;
DROP TABLE IF EXISTS STV2025011410__STAGING.groups;

CREATE TABLE IF NOT EXISTS STV2025011410__STAGING.groups(
    id INT NOT NULL, -- идентификатор группы пользователей.
    admin_id INT NOT NULL, -- идентификатор администратора.
    group_name VARCHAR(100) NOT NULL, -- название группы.
    registration_dt DATETIME NOT NULL, -- дата регистрации группы.
    is_private INT NOT NULL, -- является ли группа приватной.
    CONSTRAINT STV2025011410__STAGING_groups_id_pk PRIMARY KEY (id),
    CONSTRAINT STV2025011410__STAGING_groups_admin_id_fk FOREIGN KEY (admin_id) REFERENCES STV2025011410__STAGING.users (id),
    CONSTRAINT STV2025011410__STAGING_groups_id_unique UNIQUE (id)
)
ORDER BY id, admin_id 
SEGMENTED BY HASH(id) ALL NODES
PARTITION BY registration_dt::DATE
GROUP BY calendar_hierarchy_day(registration_dt::DATE, 3, 2);

--ALTER TABLE STV2025011410__STAGING.groups ADD CONSTRAINT STV2025011410__STAGING_groups_admin_id_fk FOREIGN KEY (admin_id) REFERENCES STV2025011410__STAGING.users (id);
--ALTER TABLE STV2025011410__STAGING.groups ADD CONSTRAINT STV2025011410__STAGING_groups_id_unique UNIQUE (id);



-- dialogs - Диалоги между пользователями

DROP TABLE IF EXISTS STV2025011410__STAGING.dialogs;

CREATE TABLE IF NOT EXISTS STV2025011410__STAGING.dialogs(
    message_id INT NOT NULL, -- это идентификатор сообщения.
    message_ts DATETIME, -- дата и время сообщения.
    message_from INT, -- идентификатор отправителя сообщения.
    message_to INT, -- идентификатор получателя.
    message VARCHAR(1000), -- текст сообщения.
    message_group INT, -- группа сообщения.
    CONSTRAINT STV2025011410__STAGING_dialogs_message_id_pk PRIMARY KEY (message_id),
    CONSTRAINT STV2025011410__STAGING_dialogs_message_from_fk FOREIGN KEY (message_from) REFERENCES STV2025011410__STAGING.users (id),
    CONSTRAINT STV2025011410__STAGING_dialogs_message_to_fk FOREIGN KEY (message_to) REFERENCES STV2025011410__STAGING.users (id),
    CONSTRAINT STV2025011410__STAGING_dialogs_message_group_fk FOREIGN KEY (message_group) REFERENCES STV2025011410__STAGING.groups (id),
    CONSTRAINT STV2025011410__STAGING_dialogs_message_id_unique UNIQUE (message_id)
)
ORDER BY message_id
SEGMENTED BY HASH(message_id) ALL NODES
PARTITION BY message_ts::DATE
GROUP BY calendar_hierarchy_day(message_ts::DATE, 3, 2);

--ALTER TABLE STV2025011410__STAGING.dialogs ADD CONSTRAINT STV2025011410__STAGING_dialogs_message_from_fk FOREIGN KEY (message_from) REFERENCES STV2025011410__STAGING.users (id);
--ALTER TABLE STV2025011410__STAGING.dialogs ADD CONSTRAINT STV2025011410__STAGING_dialogs_message_to_fk FOREIGN KEY (message_to) REFERENCES STV2025011410__STAGING.users (id);
--ALTER TABLE STV2025011410__STAGING.dialogs ADD CONSTRAINT STV2025011410__STAGING_dialogs_message_group_fk FOREIGN KEY (message_group) REFERENCES STV2025011410__STAGING.groups (id);

-- group_log - Логи групп

DROP TABLE IF EXISTS STV2025011410__STAGING.group_log;

CREATE TABLE IF NOT EXISTS STV2025011410__STAGING.group_log
(
    group_id INT, -- уникальный идентификатор группы
    user_id INT, -- уникальный идентификатор пользователя
    user_id_from INT, -- поле для отметки о том, что пользователь не сам вступил в группу
    event VARCHAR(20), -- действие, которое совершено пользователем user_id (create, add, leave)
    datetime DATETIME -- время совершения event
)
ORDER BY group_id, user_id
SEGMENTED BY HASH(user_id) ALL NODES
PARTITION BY datetime::DATE 
GROUP BY calendar_hierarchy_day(datetime::DATE, 3, 2);

SELECT table_schema, table_name, ordinal_position as col_no, column_name, data_type, is_nullable
FROM v_catalog.columns 
WHERE table_schema = 'STV2025011410__STAGING'
ORDER BY table_schema, table_name, col_no;

SELECT *
FROM STV2025011410__STAGING.users
LIMIT 5;

SELECT *
FROM STV2025011410__STAGING.groups
LIMIT 5;

SELECT *
FROM STV2025011410__STAGING.dialogs
LIMIT 5;

SELECT *
FROM STV2025011410__STAGING.group_log
LIMIT 5;

-- DDL хаба h_users

drop table if exists STV2025011410__DWH.h_users;

create table STV2025011410__DWH.h_users
(
    hk_user_id bigint primary key,
    user_id      int,
    registration_dt datetime,
    load_dt datetime,
    load_src varchar(20)
)
order by load_dt
SEGMENTED BY hk_user_id all nodes
PARTITION BY load_dt::date
GROUP BY calendar_hierarchy_day(load_dt::date, 3, 2);
;



-- DDL хаба h_groups

DROP TABLE IF EXISTS STV2025011410__DWH.h_groups

CREATE TABLE IF NOT EXISTS STV2025011410__DWH.h_groups
(
    hk_group_id BIGINT PRIMARY KEY,
    group_id INT,
    registration_dt DATETIME,
    load_dt DATETIME,
    load_src VARCHAR(20)
)
ORDER BY load_dt
SEGMENTED BY hk_group_id ALL NODES
PARTITION BY load_dt::DATE
GROUP BY calendar_hierarchy_day(load_dt::DATE, 3, 2)
;



-- DDL хаба h_dialogs

DROP TABLE IF EXISTS STV2025011410__DWH.h_dialogs

CREATE TABLE IF NOT EXISTS STV2025011410__DWH.h_dialogs
(
    hk_message_id BIGINT PRIMARY KEY,
    message_id INT,
    message_ts DATETIME,
    load_dt DATETIME,
    load_src VARCHAR(20)
)
ORDER BY load_dt
SEGMENTED BY hk_message_id ALL NODES
PARTITION BY load_dt::DATE
GROUP BY calendar_hierarchy_day(load_dt::DATE, 3, 2)
;

-- DDL таблицы связи l_user_message

drop table if exists STV2025011410__DWH.l_user_message;

create table IF NOT EXISTS STV2025011410__DWH.l_user_message
(
hk_l_user_message bigint primary key,
hk_user_id bigint not null CONSTRAINT fk_l_user_message_user REFERENCES STV2025011410__DWH.h_users (hk_user_id),
hk_message_id bigint not null CONSTRAINT fk_l_user_message_message REFERENCES STV2025011410__DWH.h_dialogs (hk_message_id),
load_dt datetime,
load_src varchar(20)
)
order by load_dt
SEGMENTED BY hk_user_id all nodes
PARTITION BY load_dt::date
GROUP BY calendar_hierarchy_day(load_dt::date, 3, 2);



-- DDL таблицы связи l_admins

DROP TABLE IF EXISTS STV2025011410__DWH.l_admins;

CREATE TABLE IF NOT EXISTS STV2025011410__DWH.l_admins
(
    hk_l_admin_id BIGINT PRIMARY KEY,
    hk_user_id BIGINT NOT NULL CONSTRAINT fk_l_admins_user REFERENCES STV2025011410__DWH.h_users (hk_user_id),
    hk_group_id BIGINT NOT NULL CONSTRAINT fk_l_admins_group REFERENCES STV2025011410__DWH.h_groups (hk_group_id),
    load_dt DATETIME,
    load_src VARCHAR(20)
)
ORDER BY load_dt
SEGMENTED BY hk_l_admin_id ALL NODES
PARTITION BY load_dt::DATE
GROUP BY calendar_hierarchy_day(load_dt::DATE, 3, 2);



-- DDL таблицы связи l_groups_dialogs

DROP TABLE IF EXISTS STV2025011410__DWH.l_groups_dialogs;

CREATE TABLE IF NOT EXISTS STV2025011410__DWH.l_groups_dialogs
(
    hk_l_groups_dialogs BIGINT PRIMARY KEY,
    hk_message_id BIGINT NOT NULL CONSTRAINT fk_l_groups_dialogs_message REFERENCES STV2025011410__DWH.h_dialogs (hk_message_id),
    hk_group_id BIGINT NOT NULL CONSTRAINT fk_l_groups_dialogs_group REFERENCES STV2025011410__DWH.h_groups (hk_group_id),
    load_dt DATETIME,
    load_src VARCHAR(20)
)
ORDER BY load_dt
SEGMENTED BY hk_l_groups_dialogs ALL NODES
PARTITION BY load_dt::DATE 
GROUP BY calendar_hierarchy_day(load_dt::DATE, 3, 2);

-- DDL таблицы связи l_user_group_activity

DROP TABLE If EXISTS STV2025011410__DWH.l_user_group_activity;

CREATE TABLE IF NOT EXISTS STV2025011410__DWH.l_user_group_activity
(
    hk_l_user_group_activity BIGINT PRIMARY KEY,
    hk_user_id BIGINT CONSTRAINT l_user_group_activity_user REFERENCES STV2025011410__DWH.h_users(hk_user_id),
    hk_group_id BIGINT CONSTRAINT l_user_group_activity_group REFERENCES STV2025011410__DWH.h_groups(hk_group_id),
    load_dt DATETIME,
    load_src VARCHAR(20)
)
ORDER BY hk_l_user_group_activity
SEGMENTED BY HASH(hk_l_user_group_activity) ALL NODES
PARTITION BY load_dt::DATE 
GROUP BY calendar_hierarchy_day(load_dt::DATE, 3, 2);

-- Создаие таблицы сателлита s_admins

drop table if exists STV2025011410__DWH.s_admins;

create table STV2025011410__DWH.s_admins
(
    hk_admin_id bigint not null CONSTRAINT fk_s_admins_l_admins REFERENCES STV2025011410__DWH.l_admins (hk_l_admin_id),
    is_admin boolean,
    admin_from datetime,
    load_dt datetime,
    load_src varchar(20)
)
order by load_dt
SEGMENTED BY hk_admin_id all nodes
PARTITION BY load_dt::date
GROUP BY calendar_hierarchy_day(load_dt::date, 3, 2);



-- Создаие таблицы сателлита s_group_name

DROP TABLE IF EXISTS STV2025011410__DWH.s_group_name;

CREATE TABLE IF NOT EXISTS STV2025011410__DWH.s_group_name
(
    -- В названии внешнего ключа название текущей таблицы 
    -- и таблицы, и которой танется идентификатор
    hk_group_id BIGINT NOT NULL CONSTRAINT fk_s_group_name_h_groups REFERENCES STV2025011410__DWH.h_groups(hk_group_id),
    group_name VARCHAR(100),
    load_dt DATETIME,
    load_src VARCHAR(20)
)
ORDER BY load_dt
SEGMENTED BY hk_group_id ALL NODES
PARTITION BY load_dt::DATE
GROUP BY calendar_hierarchy_day(load_dt::DATE, 3, 2);



-- Создаие таблицы сателлита s_group_private_status

DROP TABLE IF EXISTS STV2025011410__DWH.s_group_private_status;

CREATE TABLE IF NOT EXISTS STV2025011410__DWH.s_group_private_status
(
    hk_group_id BIGINT NOT NULL CONSTRAINT fk_s_group_private_status_h_groups REFERENCES STV2025011410__DWH.h_groups(hk_group_id),
    is_private INT,
    load_dt DATETIME,
    load_src VARCHAR(20)
)
ORDER BY load_dt
SEGMENTED BY hk_group_id ALL NODES
PARTITION BY load_dt::DATE 
GROUP BY calendar_hierarchy_day(load_dt::DATE, 3, 2);



-- Создаие таблицы сателлита s_dialog_info

DROP TABLE IF EXISTS STV2025011410__DWH.s_dialog_info;

CREATE TABLE IF NOT EXISTS STV2025011410__DWH.s_dialog_info
(
    hk_message_id BIGINT NOT NULL CONSTRAINT fk_s_dialog_info_h_dialogs REFERENCES STV2025011410__DWH.h_dialogs(hk_message_id),
    message VARCHAR(1000),
    message_from INT,
    message_to INT,
    load_dt DATETIME,
    load_src VARCHAR(20)
)
ORDER BY load_dt
SEGMENTED BY hk_message_id ALL NODES
PARTITION BY load_dt::DATE 
GROUP BY calendar_hierarchy_day(load_dt::DATE, 3, 2);



-- Создаие таблицы сателлита s_user_chatinfo

DROP TABLE IF EXISTS STV2025011410__DWH.s_user_chatinfo;

CREATE TABLE IF NOT EXISTS STV2025011410__DWH.s_user_chatinfo
(
    hk_user_id BIGINT NOT NULL CONSTRAINT fk_s_user_chatinfo_h_users REFERENCES STV2025011410__DWH.h_users(hk_user_id),
    chat_name VARCHAR(200),
    load_dt DATETIME,
    load_src VARCHAR(20)
)
ORDER BY load_dt
SEGMENTED BY hk_user_id ALL NODES
PARTITION BY load_dt::DATE 
GROUP BY calendar_hierarchy_day(load_dt::DATE, 3, 2);



-- Создаие таблицы сателлита s_user_socdem

DROP TABLE IF EXISTS STV2025011410__DWH.s_user_socdem;

CREATE TABLE IF NOT EXISTS STV2025011410__DWH.s_user_socdem
(
    hk_user_id BIGINT NOT NULL CONSTRAINT fk_s_user_socdem_h_users REFERENCES STV2025011410__DWH.h_users(hk_user_id),
    --chat_name VARCHAR(200), -- СПОРНО, Т.К, ЕСТЬ В s_user_chatinfo
    country VARCHAR(200),
    age INT,
    load_dt DATETIME,
    load_src VARCHAR(20)
)
ORDER BY load_dt
SEGMENTED BY hk_user_id ALL NODES
PARTITION BY load_dt::DATE 
GROUP BY calendar_hierarchy_day(load_dt::DATE, 3, 2);

-- Создаие таблицы сателлита s_auth_history

DROP TABLE IF EXISTS STV2025011410__DWH.s_auth_history;

CREATE TABLE IF NOT EXISTS STV2025011410__DWH.s_auth_history
(
    hk_l_user_group_activity BIGINT NOT NULL CONSTRAINT fk_s_auth_history_hk_l_user_group_activity REFERENCES STV2025011410__DWH.l_user_group_activity(hk_l_user_group_activity),
    user_id_from INT,
    event VARCHAR(20),
    event_dt DATETIME,
    load_dt DATETIME,
    load_src VARCHAR(20)
)
ORDER BY hk_l_user_group_activity
SEGMENTED BY HASH(hk_l_user_group_activity) ALL NODES
PARTITION BY load_dt::DATE
GROUP BY calendar_hierarchy_day(load_dt::DATE, 3, 2);

select table_schema, table_name, ordinal_position as col_no, column_name, data_type,  
is_nullable
from v_catalog.columns 
where table_schema = 'STV2025011410__DWH'
order by table_schema, table_name, col_no;

-- Заполнение хаба h_users

INSERT INTO STV2025011410__DWH.h_users(hk_user_id, user_id,registration_dt,load_dt,load_src)
select
       hash(id) as  hk_user_id,
       id as user_id,
       registration_dt,
       now() as load_dt,
       's3' as load_src
       from STV2025011410__STAGING.users
where hash(id) not in (select hk_user_id from STV2025011410__DWH.h_users);



-- Заполнение хаба h_groups

INSERT INTO STV2025011410__DWH.h_groups(hk_group_id, group_id, registration_dt, load_dt, load_src)
SELECT
    	HASH(id) AS hk_group_id,
    	id AS group_id,
    	registration_dt,
    	NOW() AS load_dt,
    	's3' AS load_src
FROM STV2025011410__STAGING.groups
WHERE HASH(id) NOT IN (SELECT hk_group_id FROM STV2025011410__DWH.h_groups);



-- Заполнение хаба h_dialogs

INSERT INTO STV2025011410__DWH.h_dialogs(hk_message_id, message_id, message_ts, load_dt, load_src)
SELECT
    	HASH(message_id) AS hk_message_id,
    	message_id,
    	message_ts,
    	NOW() AS load_dt,
    	's3' AS load_src
FROM STV2025011410__STAGING.dialogs
WHERE HASH(message_id) NOT IN (SELECT hk_message_id FROM STV2025011410__DWH.h_dialogs)

-- Заполнение таблицы связи l_admins

INSERT INTO STV2025011410__DWH.l_admins(hk_l_admin_id, hk_group_id,hk_user_id,load_dt,load_src)
select
    	hash(hg.hk_group_id,hu.hk_user_id),
    	hg.hk_group_id,
    	hu.hk_user_id,
    	now() as load_dt,
    	's3' as load_src
from STV2025011410__STAGING.groups as g
left join STV2025011410__DWH.h_users as hu on g.admin_id = hu.user_id
left join STV2025011410__DWH.h_groups as hg on g.id = hg.group_id
where hash(hg.hk_group_id,hu.hk_user_id) not in (select hk_l_admin_id from STV2025011410__DWH.l_admins);



-- Заполнение таблицы связи l_user_message

INSERT INTO STV2025011410__DWH.l_user_message(hk_l_user_message, hk_user_id, hk_message_id, load_dt, load_src)
SELECT
    	HASH(hu.hk_user_id, hd.hk_message_id),
    	hu.hk_user_id, 
    	hd.hk_message_id,
    	NOW() AS load_dt,
    	's3' AS load_src
FROM STV2025011410__STAGING.dialogs AS d
INNER JOIN STV2025011410__DWH.h_users AS hu ON hu.user_id = d.message_from
INNER JOIN STV2025011410__DWH.h_dialogs AS hd ON hd.message_id = d.message_id 
WHERE HASH(hu.hk_user_id, hd.hk_message_id) NOT IN (SELECT hk_l_user_message FROM STV2025011410__DWH.l_user_message);



-- Заполнение таблицы связи l_groups_dialogs

INSERT INTO STV2025011410__DWH.l_groups_dialogs(hk_l_groups_dialogs, hk_message_id, hk_group_id, load_dt, load_src)
SELECT
    	HASH(hd.hk_message_id, hg.hk_group_id),
    	hd.hk_message_id, 
    	hg.hk_group_id,
    	NOW() AS load_dt,
    	's3' AS load_src
FROM STV2025011410__STAGING.dialogs AS d
LEFT JOIN STV2025011410__DWH.h_groups AS hg ON hg.group_id = d.message_group
LEFT JOIN STV2025011410__DWH.h_dialogs AS hd ON hd.message_id = d.message_id 
WHERE HASH(hd.hk_message_id, hg.hk_group_id) NOT IN (SELECT hk_l_groups_dialogs FROM STV2025011410__DWH.l_groups_dialogs);

-- Заполнение таблицы связи l_user_group_activity

INSERT INTO STV2025011410__DWH.l_user_group_activity(hk_l_user_group_activity, hk_user_id, hk_group_id, load_dt, load_src)
SELECT
    HASH(hk_user_id, hk_group_id),
    hu.hk_user_id,
    hg.hk_group_id,
    NOW(),
    's3'
FROM STV2025011410__STAGING.group_log AS gl
LEFT JOIN STV2025011410__DWH.h_users AS hu ON hu.user_id = gl.user_id
LEFT JOIN STV2025011410__DWH.h_groups AS hg ON hg.group_id = gl.group_id
WHERE HASH(hk_user_id, hk_group_id) NOT IN (SELECT hk_l_user_group_activity FROM STV2025011410__DWH.l_user_group_activity);

-- Заполение таблицы сателлита s_admins

INSERT INTO STV2025011410__DWH.s_admins(hk_admin_id, is_admin,admin_from,load_dt,load_src)
select 
    la.hk_l_admin_id,
    True as is_admin,
    hg.registration_dt,
    now() as load_dt,
    's3' as load_src
from STV2025011410__DWH.l_admins as la
left join STV2025011410__DWH.h_groups as hg on la.hk_group_id = hg.hk_group_id;



-- Заполение таблицы сателлита s_group_name

INSERT INTO STV2025011410__DWH.s_group_name(hk_group_id, group_name, load_dt, load_src)
SELECT
    hg.hk_group_id,
    g.group_name,
    NOW() AS load_dt,
    's3' AS load_src
FROM STV2025011410__DWH.h_groups AS hg
LEFT JOIN STV2025011410__STAGING.groups AS g ON g.id = hg.group_id;



-- Заполение таблицы сателлита s_group_private_status

INSERT INTO STV2025011410__DWH.s_group_private_status(hk_group_id, is_private, load_dt, load_src)
SELECT
    hg.hk_group_id,
    g.is_private,
    NOW() AS load_dt,
    's3' AS load_src
FROM STV2025011410__DWH.h_groups AS hg
LEFT JOIN STV2025011410__STAGING.groups AS g ON g.id = hg.group_id;



-- Заполение таблицы сателлита s_dialog_info

INSERT INTO STV2025011410__DWH.s_dialog_info(hk_message_id, message, message_from, message_to, load_dt, load_src)
SELECT
    hd.hk_message_id,
    d.message,
    d.message_from,
    d.message_to,
    NOW() AS load_dt,
    's3' AS load_src
FROM STV2025011410__DWH.h_dialogs AS hd
LEFT JOIN STV2025011410__STAGING.dialogs AS d ON d.message_id = hd.message_id;



-- Заполение таблицы сателлита s_user_chatinfo

INSERT INTO STV2025011410__DWH.s_user_chatinfo(hk_user_id, chat_name, load_dt, load_src)
SELECT
    hu.hk_user_id,
    u.chat_name,
    NOW() AS load_dt,
    's3' AS load_src
FROM STV2025011410__DWH.h_users AS hu
LEFT JOIN STV2025011410__STAGING.users AS u ON u.id = hu.user_id;



-- Заполение таблицы сателлита s_user_socdem

INSERT INTO STV2025011410__DWH.s_user_socdem(hk_user_id, /*chat_name,*/ country, age, load_dt, load_src)
SELECT
    hu.hk_user_id,
    --u.chat_name,
    u.country,
    u.age,
    NOW() AS load_dt,
    's3' AS load_src
FROM STV2025011410__DWH.h_users AS hu
LEFT JOIN STV2025011410__STAGING.users AS u ON u.id = hu.user_id;

-- Заполение таблицы сателлита s_auth_history

INSERT INTO STV2025011410__DWH.s_auth_history(hk_l_user_group_activity, user_id_from, event, event_dt, load_dt, load_src)
SELECT
    luga.hk_l_user_group_activity,
    gl.user_id_from,
    gl.event,
    gl.datetime,
    NOW(),
    's3'
FROM STV2025011410__STAGING.group_log AS gl
LEFT JOIN STV2025011410__DWH.h_users AS hu ON hu.user_id = gl.user_id
LEFT JOIN STV2025011410__DWH.h_groups AS hg ON hg.group_id = gl.group_id
INNER JOIN STV2025011410__DWH.l_user_group_activity AS luga ON luga.hk_user_id = hu.hk_user_id AND luga.hk_group_id = hg.hk_group_id
WHERE luga.hk_l_user_group_activity NOT IN (SELECT hk_l_user_group_activity FROM STV2025011410__DWH.s_auth_history);

SELECT *
FROM STV2025011410__DWH.h_users
LIMIT 5;

SELECT *
FROM STV2025011410__DWH.h_groups
LIMIT 5;

SELECT *
FROM STV2025011410__DWH.h_dialogs
LIMIT 5;

SELECT *
FROM STV2025011410__DWH.l_admins
LIMIT 5;