SQLの抽出条件について

Question

前提・実現したいこと

対象条件のSQL文が分からないためご助力いただけますでしょうか。
抽出したい条件は以下です。

・detailsにAAAをもつsubscriptionのstatusがACTIVEなuser
かつ
・detailsにAAAをもつsubscription以外は、全てstatusがCLOSEDである（もしくは存在しない）user

AAAのsubscriptionがACTIVEであっても、そのuserのdetailsがAAAでないACTIVEなsubscriptionを持っている場合は、対象外としたいです。

以下のテーブルの例ですと、user_idが3と6のuserを抽出したいと思っています。

対象のテーブル

テーブル名：subscriptions

id	subscription_id	user_id	status	details
1	1	1	ACTIVE	bronze
2	2	2	ACTIVE	AAA
3	3	2	ACTIVE	silver
4	4	3	ACTIVE	AAA
5	5	3	CLOSED	silver
6	6	4	CLOSED	AAA
7	7	5	CLOSED	AAA
8	8	5	ACTIVE	gold
9	9	5	CLOSED	bronze
10	10	6	ACTIVE	AAA

試したこと

以下SQLを実行しましたが、うまくいきませんでした。

SELECT * FROM subscriptions as s1
INNER JOIN subscriptions as s2
ON s1.user_id not in (s2.user_id)
AND s1.status = 'ACTIVE'
AND s1.details like 'AAA'
AND s2.status = 'ACTIVE'
AND s2.details not like 'AAA'

SELECT * FROM subscriptions as s1
INNER JOIN subscriptions as s2
ON s1.user_id <> s2.user_id
AND s1.status = 'ACTIVE'
AND s1.details like 'AAA'
AND s2.status = 'ACTIVE'
AND s2.details not like 'AAA'

補足情報（FW/ツールのバージョンなど）

PostgreSQL

Comments

[maisumakun]

> うまくいきませんでした。

それぞれどのような結果が得られたのですか？

[yambejp]

定義があいまい

>detailsにAAAをもつsubscriptionのstatusがACTIVEなuser
以下どうとでも取れます

(1)特定のレコードのdetailsがAAAかつstatusがACTIVEというデータを持つuser
(2)どこかのレコードのdetailsがAAAで、別の（同じでもよい）レコードのstatusがACTIVEを持つuser
(3)detailsがAAAレコードを持ち、statusの全てがACTIVEなuser

[退会済みユーザー]

サンプルデータの2と4と10の違い、6と7の違いがわからないので、隠している条件や省いてしまったデータによる絞り込みも必要なのではないでしょうか？

[vestrade]

分かりづらい表現となってしまい申し訳ありません。
ベストアンサーでやりたいことができました。
ありがとうございました。

Answer 1

これはどうでしょうか？

select user_id
from subscriptions
group by user_id
having count(case when ("status" = 'ACTIVE' and details='AAA') then id else NULL end) = 1
and count(case when ("status" = 'ACTIVE' and details!='AAA') then id else NULL end) = 0;

Answer 2

いまいち読み取れんけどこれでいいんかな？
detailsにAAA、statusがACTIVEなユーザーの内、
他のsubscriptionsに、detailsにAAA以外のstatusがCLOSED以外のデータを持っていないもの
言い直してもややこしい…

select *
from subscriptions A
where A.details='AAA' and A.status='ACTIVE'
  and not exists(
      select 0
      from subscriptions B
      where B.user_id=A.user_id
        and B.details<>'AAA'
        and B.status<>'CLOSED'
    )

Comments

[vestrade]

意図を読み取ってくださりありがとうございます。
もう少し分かりやすい表現をしたかったのですが、語彙が足りずすみませんでした。

[sousuke]

やりたいことをプログラムに近い日本語に変換することは
どんな言語を書くにしても大事な要素なので落ち着いて考えるといいと思います。

例えば
where A.details='AAA' and A.status='ACTIVE'
で「detailsにAAA、statusがACTIVEな」データがとれますし
この時点でサンプルだとidは2,4,10の3行だけになります。

このid:2をさらに(and)消すことになるのですがその条件が
「detailsにAAA以外のstatusがCLOSED以外のデータを持っていないuser_id」となるので
not existsのselect文につながります。