SQLite 3.27

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

select sqlite_version();

sqlite_version()

3.27.2

-- window functions (3.25) and UPSERT (3.24) are both available here
create table pet (owner text, name text);
insert into pet values ('ann','rex'),('ann','bo'),('bob','sam');

3 rows affected

select owner, name, count(*) over (partition by owner) as owners_pets
from pet order by owner, name;

owner
name
owners_pets

ann
bo
2

ann
rex
2

bob
sam
1

-- UPSERT: on conflict, add to the existing row instead of failing
create table stock (sku text primary key, qty int);
insert into stock values ('apple',10);
insert into stock values ('apple',5)
  on conflict(sku) do update set qty = qty + excluded.qty;
select * from stock;

1 row affected

1 row affected

sku
qty

apple
15