Event Store Summing Number of Expired Items using SQL

Question

I have the following table:

+---------+---------+------------------+----------------------+----------------------+
| shop_id | card_id |      event       |   event_timestamp    |     card_expires     |
+---------+---------+------------------+----------------------+----------------------+
|       1 |       1 | some event       | 2019-10-01T00:00:00z | 2019-10-10T00:00:00Z |
|       1 |       1 | some other event | 219-10-02T00:00:00Z  | 2019-10-10T00:00:00Z |
|       1 |       2 | an event         | 2019-10-03T00:00:00Z | 2019-10-29T00:00:00Z |
|       1 |       2 | another event    | 2019-10-04T00:00:00Z | 2019-10-29T00:00:00Z |
|       1 |       3 | foo event        | 2019-10-03T00:00:00Z | 2019-10-10T00:00:00Z |
|       1 |       3 | bar event        | 2019-10-04T00:00:00Z | 2019-10-10T00:00:00Z |
+---------+---------+------------------+----------------------+----------------------+

I'm trying to find the total number of expired cards for a shop_id. If we say that current timestamp is 2019-10-20T00:00:00Z then card_id 1 and 2 will be expired.

Expected Result:

+---------+-------------------------+
| shop_id | number_of_expired_cards |
+---------+-------------------------+
|       1 |                       2 |
+---------+-------------------------+

Ive gone around in circles with INNER JOIN, CASE and GROUP BY. I can identify the cards that have expired but then when I try to sum them into the results table, I get bigger values than I need. Totally stuck ...

Answer 1

SELECT shop_id, COUNT(DISTINCT card_id) number_of_expired_cards 
FROM sourcetable
WHERE card_expires < GETDATE()
GROUP BY shop_id