SQL Server 2022

By using db<>fiddle, you agree to license everything you submit by Creative Commons CC0.
create table store (
  sID integer primary key,
  sName varchar(255) not null
);
insert into store(sID, sName) values (1, 'Food & Stuff'), (2, 'Totally Honest Bills'), (3, 'Good Cheap Goods');
3 rows affected

create table product (
  pID integer primary key
);
insert into product(pID) values (1), (2), (3), (4), (5), (6), (7), (8), (9), (10), (11), (12), (13), (14), (15);
15 rows affected

create table purchase (
  sID integer not null,
  constraint purchase_store_fk foreign key(sID) references store,
  pID integer not null,
  constraint purchase_product_fk foreign key(pID) references product,
  date date not null
);
insert into purchase(sID, pID, date) values
  -- > 10 distinct purchases on 2018-08-27
  (1, 1, '2018-08-27'), (1, 1, '2018-08-27'), (1, 2, '2018-08-27'), (1, 3, '2018-08-27'), (1,4, '2018-08-27'), (1,7, '2018-08-27'), (1,8, '2018-08-27'), (1,9, '2018-08-27'), (1, 10, '2018-08-27'), (1, 11, '2018-08-27'), (1,15, '2018-08-27'),
  -- > 10 distinct purchases on 2018-08-30
  (1, 1, '2018-08-30'), (1, 1, '2018-08-30'), (1, 2, '2018-08-30'), (1, 3, '2018-08-30'), (1,4, '2018-08-30'), (1,5, '2018-08-30'), (1,7, '2018-08-30'), (1,8, '2018-08-30'), (1,9, '2018-08-30'), (1, 10, '2018-08-30'), (1, 11, '2018-08-30'), (1,15, '2018-08-30'),
  -- < 10 distinct purchases on 2018-08-15
  (1, 1, '2018-08-15'), (1, 1, '2018-08-15'), (1, 2, '2018-08-15'), (1, 9, '2018-08-15'), (1,4, '2018-08-15'), (1,9, '2018-08-15'), (1, 9, '2018-08-15'), (1,9, '2018-08-15'), (1, 9, '2018-08-15'), (1, 9, '2018-08-15'), (1,9, '2018-08-15'),
  -- > 10 purchases, but not distinct
  (2, 1, '2018-08-27'), (2, 2, '2018-08-27'),  (2, 1, '2018-08-27'), (2, 2, '2018-08-27'),  (2, 1, '2018-08-27'), (2, 2, '2018-08-27'),  (2, 1, '2018-08-27'), (2, 2, '2018-08-27'),  (2, 1, '2018-08-27'), (2, 2, '2018-08-27'),
  -- > 10 distinct purchases, wrong date
  (3, 3, '2018-08-28'), (3, 3, '2018-08-28'), (3, 2, '2018-08-28'), (3, 3, '2018-08-28'), (3,4, '2018-08-28'), (3,7, '2018-08-28'), (3,8, '2018-08-28'), (3,9, '2018-08-28'), (3,15, '2018-08-28');
53 rows affected

-- All distinct purchases by store and date
select
  store.sID,
  store.sName,
  "date",
  count(distinct pID) as "Num Different Products"
from purchase
join store on store.sID = purchase.sID
group by store.sID, store.sName, date


sID
sName
date
Num Different Products


1
Food & Stuff
2018-08-15
4

1
Food & Stuff
2018-08-27
10

2
Totally Honest Bills
2018-08-27
2

3
Good Cheap Goods
2018-08-28
7

1
Food & Stuff
2018-08-30
11


-- Only > 10 distinct purchases by day
select
  store.sID,
  store.sName,
  "date",
  count(distinct pID) as "Num Different Products"
from purchase
join store on store.sID = purchase.sID
group by store.sID, store.sName, date
having count(distinct pID) >= 10;


sID
sName
date
Num Different Products


1
Food & Stuff
2018-08-27
10

1
Food & Stuff
2018-08-30
11
sID	sName	date	Num Different Products
1	Food & Stuff	2018-08-15	4
1	Food & Stuff	2018-08-27	10
2	Totally Honest Bills	2018-08-27	2
3	Good Cheap Goods	2018-08-28	7
1	Food & Stuff	2018-08-30	11