【SQL】特定の条件で集計対象から除外したい

前提・実現したいこと

・SQL server2019

SQLにて2つのテーブルからデータ抽出を行おうとしています。
最終的に出したい結果としては以下の表のように購入数/配信数(=メール配信して購入してくれた人の数)を調査したいです。
|メール名|配信月|配信数|購入数|
|:--|:--:|--:|
|MailA|2021-01|2|1|
|MailB|2021-01|2|1|
抽出条件

①配信日より購入日の値が大きいこと(=メール配信後に購入していること)
②既に購入していたのにも関わらず配信してしまっていた人は配信数、購入数ともに集計から除外すること
→下記の使用テーブルであればItoさんとKawadaさんは②に該当する為、集計対象から除外したい
※メール名ごとの配信先氏名と購入者名をカウントしてそれぞれ配信数、購入数を集計する

使用テーブル

配信データテーブル

メール名	配信先氏名	配信日
MailA	Sato	2021-01-01
MailB	Yamada	2021-01-12
MailB	Ito	2021-01-06
MailA	kawada	2021-01-11
MailA	Obama	2021-01-02

購入履歴データテーブル

購入者名	購入日
Sato	2021-01-02
Yamada	2021-01-14
Ito	2021-01-02
kawada	2021-01-09

発生している問題

既に購入していたのにも関わらず配信してしまっていた人を配信数、購入数から除外する処理が上手く実装できず、困っています。
下記SQLではメール配信したけれど購入しなかった人まで除外されてしまっているようで、機体動作ではありません。（購入履歴データテーブルをleft outer joinしており、購入しなかった人の購入日カラムにはNullが入る為？）
どうにか購入しなかった人も配信数には含めつつ、既に購入していたのにも関わらず配信してしまっていた人は配信数と購入数から除外したいのですが良い方法が思い浮かばずの状態です。

試したSQL

SQL
1select
2 snt.メール名,
3 convert(varchar,substring(snt.配信日,1,6),112) as 配信月,
4 count(snt.配信先氏名) as 配信数,
5 count(cvt.購入者名) as 購入数
6
7from
8 配信データテーブル as snt
9left outer join
10 購入履歴データテーブル as cvt
11on
12 snt.配信先氏名 = cvt.購入者名
13and
14--抽出条件①
15 snt.配信日 <= cvt.購入日
16
17where
18--抽出条件②
19 not (cvt.購入日 < snt.配信日)
20
21group by
22 メール名,
23 convert(varchar,substring(snt.配信日,1,6),112)

補足

SQL初心者でして、説明下手で申し訳ございません。。
色々なサイトを見漁って調べたのですがなかなか良い解決策が浮かばず、
何か良い方法がございましたらご教示いただけますと幸いです。

行動規範の内容に同意します

回答1件

ベストアンサー

ご希望の結果は理解できましたけど
集計に対する前提条件やご考慮は少し不足しているように思います。

ご提示のサンプルデータでは

配信日フィールド、購入日フィールドとも、値が日付になっていますけど
実際のテーブルに登録されている値は
日付だけでなく、時刻まで記録されたタイムスタンプになっているでしょうか。
通常、何らかのイベントやトリガーをトレースする単位は
「日」ではなく「日時」であることが多いと思います。

SQL文を拝見する限り、把握なさってはいるでしょうけど「 購入 」に該当するかは、

メールを配信してから、次回配信日までの間 とする必要があるでしょう。
上限を決めないと、配信後１年経過した人が該当者になってしまう
なんて可能性がありますし
ひと月に何回もメルマガを配信する場合においては
購入日と合致させるメルマガが1 対 1の関係になるような工夫も必要です。

2. 次回までの配信日に複数回購入していただいた場合でも
購入者に対する集計は、配信直後だけの1回きりとする必要があるでしょう。
( そうでないと、Yes, No 判定にはなりません )

3. ご希望の結果セットに配信月という年月の項目がありますけど
この演算フィールドでグループ化するのは適切ではないでしょう。
配信が漏れてたりなどの事情で
配信期間が月を跨いだりするとメール名を基準とした集計にはなりません。
そのため、月間表示をご希望の場合は、私が記述したSQLで申し上げると
SELECT句でformat( min( delivery ), 'yyyy-MM' )
とすればいいでしょう。

以上のことを考慮に入れてSQLを書いてみました。
※ 他に抜けがあるかもしれないですけど、とりあえず掲載します

SQL
1WITH ot ( mail, target, delivery, expiration ) AS
2(
3    SELECT メール名
4         , 配信先氏名
5         , 配信日
6         , coalesce(
7               lead( 配信日, 1 ) 
8                   over ( partition by 配信先氏名 order by 配信日
9               )
10             , datefromparts( 2099, 12, 31 )
11           )
12    FROM 配信データテーブル
13)
14  , progress ( mail, target, delivery, yesno ) AS
15(
16    SELECT ot.mail
17         , ot.target
18         , ot.delivery
19         , count( distinct it.購入者名 )
20    FROM ot 
21    LEFT JOIN 購入履歴データテーブル it
22           ON ot.target     =  it.購入者名
23          AND ot.delivery   <  it.購入日
24          AND ot.expiration >= it.購入日
25    GROUP BY ot.mail
26           , ot.target
27           , ot.delivery
28)
29
30SELECT mail              メール名
31     , min( delivery )   初回配信日
32     , max( delivery )   最終配信日
33     , count(1)          配信数
34     , sum( yesno )      購入数
35FROM progress
36GROUP BY mail
37ORDER BY mail
38;