eBayドイツ（DE）バイヤー購買傾向｜実データで読み解く自動車・ポップカルチャー大国の需要

with raw as (
  select json_value(data, "$.category.CategoryName") cat,
    cast(json_value(data, "$.transaction_price") as float64) price
  from [販売データテーブル]
  where operation = "CREATE"
  and timestamp >= timestamp("2025-11-01")
  and timestamp < timestamp("2026-04-01")
  and json_value(data, "$.buyer_detail.address.Country") = "DE"
  and cast(json_value(data, "$.transaction_price") as float64) > 0
),
categorized as (
  select case
    when cat like "%Animation%" or cat like "%Animationskunst%" then "アニメグッズ"
    when cat like "%Figure%" or cat like "%Toy%" or cat like "%Figuren%"
      or cat like "%Spielzeug%" or cat like "%Aufstellfiguren%" then "フィギュア・おもちゃ"
    when cat like "%Music%" or cat like "%CD%" or cat like "%Musik%" then "CD/音楽"
    when cat like "%Watch%" or cat like "%Uhren%" or cat like "%Armband%" then "腕時計"
    when cat like "%Diecast%" or cat like "%Verkehrsmodelle%"
      or cat like "%Modellbau%" then "ダイキャストカー・模型"
    when cat like "%Bag%" or cat like "%Handbag%" or cat like "%Taschen%" then "バッグ"
    when cat like "%Fish%" or cat like "%Lure%" or cat like "%Angelsport%" then "釣具"
    when cat like "%Video Game%" or cat like "%Videospiele%" then "ゲーム"
    when cat like "%Book%" or cat like "%Bücher%" or cat like "%Antiquarische%" then "書籍"
    when cat like "%Camera%" or cat like "%Kamera%" then "カメラ"
    when cat like "%Vinyl%" or cat like "%Record%" then "レコード"
    when cat like "%Manga%" or cat like "%Mangas%" then "マンガ"
    when cat like "%Pen%" or cat like "%Stationery%" then "文具"
    when cat like "%Card Game%" or cat like "%Sammelkartenspiele%"
      or cat like "%TCG%" then "トレーディングカード"
    when cat like "%Auto%" or cat like "%Motorrad%" or cat like "%Öl%" then "自動車パーツ"
    else "その他" end category, price
  from raw
)
select category, count(*) cnt, round(avg(price), 0) avg_price,
  round(sum(price), 0) total_sales,
  round(count(*) * 100.0 / sum(count(*)) over(), 1) share_pct
from categorized
group by 1 order by cnt desc limit 15

select case
    when price between 0.01 and 20 then "$1-20"
    when price between 20.01 and 50 then "$21-50"
    when price between 50.01 and 100 then "$51-100"
    when price between 100.01 and 200 then "$101-200"
    when price between 200.01 and 500 then "$201-500"
    else "$501+" end price_range,
  count(*) cnt,
  round(count(*) * 100.0 / sum(count(*)) over(), 1) share_pct
from (
  select cast(json_value(data, "$.transaction_price") as float64) price
  from [販売データテーブル]
  where operation = "CREATE"
  and timestamp >= timestamp("2025-11-01")
  and timestamp < timestamp("2026-04-01")
  and json_value(data, "$.buyer_detail.address.Country") = "DE"
  and cast(json_value(data, "$.transaction_price") as float64) > 0
)
group by 1 order by min(price)

-- 時間帯別（CET基準）
select case
  when extract(hour from timestamp at time zone "Europe/Berlin") between 0 and 5 then "深夜（0-5時CET）"
  when extract(hour from timestamp at time zone "Europe/Berlin") between 6 and 11 then "午前（6-11時CET）"
  when extract(hour from timestamp at time zone "Europe/Berlin") between 12 and 17 then "午後（12-17時CET）"
  else "夜間（18-23時CET）" end time_band,
  count(*) cnt, round(avg(cast(json_value(data, "$.transaction_price") as float64)), 0) avg_price
from [販売データテーブル]
where operation = "CREATE"
and timestamp >= timestamp("2025-11-01")
and timestamp < timestamp("2026-04-01")
and json_value(data, "$.buyer_detail.address.Country") = "DE"
and cast(json_value(data, "$.transaction_price") as float64) > 0
group by 1 order by cnt desc