SQL Server 2022

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

CREATE TABLE Bundle (
  "ID" INTEGER PRIMARY KEY
);

INSERT INTO Bundle
  ("ID")
VALUES
  ('1'),
  ('2'),
  ('3');

CREATE TABLE Account (
  "ID" INTEGER PRIMARY KEY
);

INSERT INTO Account
  ("ID")
VALUES
  ('1000'),
  ('1001'),
  ('1002'),
  ('1003'),
  ('1004');

CREATE TABLE AccountProduct (
  "AccountID" INTEGER,
  "ProductID" INTEGER,
  PRIMARY KEY (AccountId, ProductId)
);

INSERT INTO AccountProduct
  ("AccountID", "ProductID")
VALUES
  ('1000', '10'),
  ('1000', '20'),
  ('1000', '30'),
  ('1001', '10'),
  ('1001', '20'),
  ('1001', '30'),
  ('1001', '50'),
  ('1001', '60'),
  ('1001', '70'),
  ('1002', '50'),
  ('1002', '60'),
  ('1002', '70'),
  ('1003', '80'),
  ('1004', '10'),
  ('1004', '20'),
  ('1004', '80');

CREATE TABLE BundleProduct (
  "BundleID" INTEGER,
  "ProductID" INTEGER,
  PRIMARY KEY (BundleID, ProductID)
);

INSERT INTO BundleProduct
  ("BundleID", "ProductID")
VALUES
  ('1', '10'),
  ('1', '20'),
  ('1', '30'),
  ('2', '50'),
  ('2', '60'),
  ('2', '70'),
  ('3', '80');

31 rows affected

AccountId	BundleId	ProductsRemaining	c
1000	1	0	3
1001	1	3	6
1001	2	3	6
1002	2	0	3
1003	3	0	1
1004	3	2	3

Warning: Null value is eliminated by an aggregate or other SET operation.