CASE WHEN文の条件式について

Question

CASE WHENで躓いているので教えていだきたいです。
一回目はPCで購入したが、２回目以降SPで購入したことがあるカスタマーをCASE文で書けないか考えています。
下記のcase文を書いたのですが、SP移行ユーザーとPC移行ユーザーの結果が出てこず。おそらく下記の書き方だと”一回目PC購入の後”の前後の関係が必要なのではないかと思っています。
そこでCASE文で１回目購入はPC、２回目移行SPを利用したみたいな前後の条件を入れる式を教えて頂きたいです。

CASE
　　　　　　　　WHEN 購入回数 = １ AND 購入端末 = 'PC' then "PC新規"
    WHEN 購入回数 = １ AND 購入端末 = 'SP' then "SP新規"
    WHEN (購入回数 = １ 購入端末 = 'PC') AND (購入回数 > １ AND 購入端末 = 'sp')   then "sp移行ユーザー"　　--初回はPCで購入したが２回目移行どこかでSPを使用したユーザー
    WHEN (購入回数 = １ AND 購入端末e = 'sp') AND (購入回数 > 1 AND 購入端末 = 'PC')   then "PC移行ユーザー"     --初回はSPで購入したが２回目移行どこかでPCを使用したユーザー
    ELSE "other"
END

--

|userID|購入回数|購入端末|購入日時|
|:--|:--:|--:|
|aaaa|1|PC|2021-12-01
|aaaa|２|SP|2021-12-02
|aaaa|３|SP|2021-12-03
|bbbb|１|PC|2021-12-01
|bbbb|２|PC|2021-12-02
|bbbb|3|PC|2021-12-03
|bbbb|４|SP|2021-12-04

ほしいアウトプット

・日付を行に持ってきて週別に新規のカスタマーおよび継続ユーザーの週別推移が見たい

	１２/1	12/8
１２/1	PC新規
	SP新規
	PC移行ユーザー
	SP移行ユーザー

Comments

[neko_the_shadow]

①サンプルデータの4行目、(userid, 購入回数, 購入端末) = (aaaa, 1, PC) ですが、このuseridはbbbbであっていますか?
②userIDごとにPC新規/SP新規/SP移行ユーザー/PC移行ユーザー/OTHERを求めますか? もしくは行ごとにPC新規/SP新規/SP移行ユーザー/PC移行ユーザー/OTHERを求めますか?

[m.ts10806]

DBは利用するDBとバージョンに絞ったほうが良いかと。

[aiueo122]

neko_the_shadow　様
①について
ご指摘の通りであっています。

②について
すいません、ほしいアウトプットについて記載していなかったので記載致します。

[neko_the_shadow]

日付はどのように取得しますか

[m.ts10806]

DBは利用するDBとバージョンに絞ってください。方言があるので「あれもこれも全てに対応した書き方」が難しいシーンのほうが多いです。

[aiueo122]

neko_the_shadow　様
日付のカラムを追加しました。

[m.ts10806]

結構大事なことを再三依頼してるのですが、無視ですか。

[skys215]

userIdは求めてないのですか？

[FKM]

購入回数の最大値の購入端末がspであり、かつuser_idごとの購入端末にpcが含まれている、この条件で出せないでしょうか？

[aiueo122]

m.ts10806　様
素人で利用するDBはMySQLなのですが、バージョンがわからず記載できておらず申し訳ございません。

[FKM]

タグにSQL Serverとなっていますよ。これはMSのDBMSでOracleが所有するmysqlとは別物です。
mysqlならば、タグを入れ替えておいたほうが無難ですし、mysqlのほうが回答者も多いです。

[aiueo122]

FKM　様
ありがとうございます！
タグの削除を行いました。

[m.ts10806]

MySQLならselect version()
実行すれば確認できます（調べれば出てきます）
あとBigQueryも別物です。

[aiueo122]

m.ts10806 様
ありがとうございます。
やってみます。
わかりにくくて申し訳ないのですが、MySQLとBigquery 両方の環境で同じデータが使えるので最悪Big Queryでも回答があれば試したくBig Queryも入れております。

Answer 1

結構複雑なことをやろうとしているので、まずは下記のように分割して試してみてください。
それができたら 2つを join します。そこではじめて case 文を使います。

-- 最新購入
select 購入.userId, 最新購入.最新購入回数, 購入.端末
from 購入 inner join (
  select
    userId,
    max(購入回数) as 最新購入回数,
  from 購入
  group by userId
) as 最新購入
on 購入.userId = 最新購入.userId
and 購入.購入回数 = 最新購入.最新購入回数

-- 初回購入端末
select userID, 端末
from 購入 where 購入回数=1