SQLite 3.24 - Counting occurrences of value grouped by id without using window functions

Question

Apologies if this has been asked, but maybe I'm not familiar with the language to ask for what I want, and I looked through dozens of other questions but don't seem to be finding what works.

I'm working on a tool for a trading card game, a deck is a list of cards, I have a table mapping deckId to cardId

Given the example (rowid not shown)

deckId| cardId
-----------
1  | 321
1  | 50
1  | 100
1  | 125
2  | 321
2  | 50
2  | 99
2  | 87
3  | 50
3  | 12
3  | 5
3  | 47
4  | 999
4  | 998
4  | 997
4  | 996

What I am trying to query is for each deck, how many cards does it have that appear in more than 1 deck.

deckId  |  count(cardId)
--------------------
1  | 2 // 321 and 50 appear in both deckId=1 and deckId=2
2  | 2 // 321 and 50 appear in both deckId=1 and deckId=2
3  | 1 // 50 appears in deckId=2 and deckId=3
4  | 0 // none of the cards in deckId=4 appear in any other decks

I've tried

SELECT deckId, COUNT(cardId)
FROM table
GROUP BY deckId

but it just gives me (because each deck has 4 cards):

deckId  | count(cardId)
-----------------------
1 | 4
2 | 4
3 | 4
4 | 4

So how do I query for each deck, how many "common" cards does it have? (window functions were suggested, but i'm forced to use SQLite version 3.24 before window functions are implemented)

Answer 1

You can use window functions and aggregation:

select 
    deckId,
    sum(case when cnt > 1 then 1 else 0 end) cnt
from (
    select deckId, count(*) over(partition by cardId) cnt
    from mytable 
) t
group by deckId

Demo on DB Fiddle:

deckId | cnt
:----- | --:
1      |   2
2      |   2
3      |   1
4      |   0

---

In versions of SQLite that do not support window functions, you can emulate the window count with a subquery:

select 
    deckId,
    sum(case when cnt > 1 then 1 else 0 end) cnt
from (
    select 
        deckId, 
        (select count(*) from mytable t1 where t1.cardId = t.cardId) cnt
    from mytable t 
) t
group by deckId