SQLite 3.27

By using db<>fiddle, you agree to license everything you submit by Creative Commons CC0.

CREATE TABLE accounts (
    id INTEGER PRIMARY KEY,
    name TEXT,
    password TEXT,
    ip TEXT,
    serial TEXT,
    httppass TEXT
);
INSERT INTO accounts (id, name) VALUES 
(1, "Egor"),
(2, "Civilian");
SELECT * FROM accounts;


id
name
password
ip
serial
httppass


1
Egor
null
null
null
null

2
Civilian
null
null
null
null


CREATE TABLE connections (
    id INTEGER PRIMARY KEY,
    account_id	INTEGER NOT NULL,
    datetime	TEXT NOT NULL,
    FOREIGN KEY("account_id") REFERENCES accounts(id)
);
CREATE TRIGGER keep_3_per_account AFTER INSERT ON connections
  WHEN (SELECT COUNT(*) FROM connections WHERE account_id=NEW.account_id) > 3
  BEGIN
    DELETE FROM connections WHERE id = (SELECT id FROM connections ORDER BY id LIMIT 1);
  END;
END;
INSERT INTO connections (account_id, datetime) VALUES 
(1, "2022-03-04"),
(1, "2022-03-05"),
(1, "2022-03-06"),
(2, "2022-03-01"),
(2, "2022-03-03")
;
SELECT * FROM connections;


id
account_id
datetime


1
1
2022-03-04

2
1
2022-03-05

3
1
2022-03-06

4
2
2022-03-01

5
2
2022-03-03


SELECT account_id, datetime FROM connections WHERE account_id=1;


account_id
datetime


1
2022-03-04

1
2022-03-05

1
2022-03-06


SELECT accounts.id, name, datetime FROM accounts INNER JOIN connections on accounts.id = connections.account_id WHERE accounts.name="Egor";


id
name
datetime


1
Egor
2022-03-04

1
Egor
2022-03-05

1
Egor
2022-03-06


INSERT INTO connections (account_id, datetime) VALUES 
(1, strftime('%Y-%m-%d', 'now'));
SELECT accounts.id, name, datetime FROM accounts INNER JOIN connections on accounts.id = connections.account_id WHERE accounts.name="Egor";


id
name
datetime


1
Egor
2022-03-05

1
Egor
2022-03-06

1
Egor
2022-03-08

id	name	password	ip	serial	httppass
1	Egor	null	null	null	null
2	Civilian	null	null	null	null

id	account_id	datetime
1	1	2022-03-04
2	1	2022-03-05
3	1	2022-03-06
4	2	2022-03-01
5	2	2022-03-03