friends(user_id, friend_id)

SELECT user_id, COUNT(friend_id) AS total_friends
FROM friends
GROUP BY user_id
ORDER BY total_friends DESC
LIMIT 1;

INSERT INTO friends VALUES (1, 2), (1, 2), (1, 3);

SELECT COUNT(friend_id) FROM friends WHERE user_id = 1;
-- → вернёт 3

SELECT user_id, COUNT(DISTINCT friend_id) AS unique_friends
FROM friends
GROUP BY user_id
ORDER BY unique_friends DESC
LIMIT 1;

WHERE user_id != friend_id

git clone --branch v0.8.0 https://github.com/pgvector/pgvector.git
cd pgvector
make
sudo make install

CREATE EXTENSION vector;

CREATE TABLE items (
  id bigserial PRIMARY KEY,
  embedding vector(3)
);

INSERT INTO items (embedding) VALUES ('[1,2,3]'), ('[4,5,6]');

SELECT * FROM items
ORDER BY embedding <-> '[3,1,2]'
LIMIT 5;

SELECT AVG(embedding) FROM items;

CREATE INDEX ON items USING hnsw (embedding vector_l2_ops);

SET hnsw.ef_search = 40;

CREATE INDEX ON items USING ivfflat (embedding vector_l2_ops) WITH (lists = 100);
SET ivfflat.probes = 10;

SELECT extversion FROM pg_extension WHERE extname='vector';
ALTER EXTENSION vector UPDATE;

CREATE TABLE user_status_log (
    user_id INT,
    status TEXT,         -- 'active', 'inactive', 'banned'
    changed_at TIMESTAMP
);

WITH ranked_status AS (
  SELECT
    user_id,
    status,
    changed_at,
    ROW_NUMBER() OVER (PARTITION BY user_id ORDER BY changed_at) AS rn
  FROM user_status_log
),
status_with_next AS (
  SELECT
    user_id,
    status,
    changed_at,
    LEAD(status) OVER (PARTITION BY user_id ORDER BY changed_at) AS next_status,
    LEAD(changed_at) OVER (PARTITION BY user_id ORDER BY changed_at) AS next_changed_at
  FROM ranked_status
),
transitions AS (
  SELECT
    user_id,
    changed_at AS from_time,
    next_changed_at AS to_time,
    status AS from_status,
    next_status AS to_status
  FROM status_with_next
  WHERE next_status IS NOT NULL
),
flagged_users AS (
  SELECT DISTINCT user_id
  FROM (
    SELECT
      user_id,
      MAX(CASE WHEN from_status = 'inactive' AND to_status = 'banned' THEN 1 ELSE 0 END) AS went_inactive_then_banned,
      MIN(CASE WHEN from_status = 'banned' AND to_status = 'active' THEN 1 ELSE 0 END) AS banned_then_active
    FROM transitions
    GROUP BY user_id
  ) t
  WHERE went_inactive_then_banned = 1
)
SELECT *
FROM flagged_users;