Files
mtusi2026lab2/tests/track2_fingerprint.sql
2026-10-15 20:55:44 +03:00

95 lines
2.2 KiB
SQL

\set ON_ERROR_STOP on
\pset tuples_only on
\pset format unaligned
\pset pager off
WITH selected_messages AS (
(
SELECT
id::text AS id,
chat_id::text AS chat_id,
sender_id::text AS sender_id,
md5(text) AS text_md5,
to_char(
created_at AT TIME ZONE 'UTC',
'YYYY-MM-DD"T"HH24:MI:SS.US"Z"'
) AS created_at
FROM messages
ORDER BY created_at, id
LIMIT 1
)
UNION ALL
(
SELECT
id::text,
chat_id::text,
sender_id::text,
md5(text),
to_char(
created_at AT TIME ZONE 'UTC',
'YYYY-MM-DD"T"HH24:MI:SS.US"Z"'
)
FROM messages
ORDER BY created_at, id
OFFSET 49999
LIMIT 1
)
UNION ALL
(
SELECT
id::text,
chat_id::text,
sender_id::text,
md5(text),
to_char(
created_at AT TIME ZONE 'UTC',
'YYYY-MM-DD"T"HH24:MI:SS.US"Z"'
)
FROM messages
ORDER BY created_at, id
OFFSET 99999
LIMIT 1
)
),
distribution AS (
SELECT
chat_id::text AS chat_id,
count(*) AS message_count
FROM messages
GROUP BY chat_id
)
SELECT jsonb_build_object(
'counts',
jsonb_build_object(
'users', (SELECT count(*) FROM users),
'chats', (SELECT count(*) FROM chats),
'chat_members', (SELECT count(*) FROM chat_members),
'messages', (SELECT count(*) FROM messages),
'sessions', (SELECT count(*) FROM sessions)
),
'message_distribution',
(
SELECT jsonb_object_agg(
chat_id,
message_count
ORDER BY chat_id
)
FROM distribution
),
'selected_messages',
(
SELECT jsonb_agg(
to_jsonb(selected_messages)
ORDER BY created_at, id
)
FROM selected_messages
),
'message_indexes',
(
SELECT jsonb_agg(indexname ORDER BY indexname)
FROM pg_indexes
WHERE schemaname = 'public'
AND tablename = 'messages'
)
)::text;