💮SQLインセンティブ制度/DBアワー出張編 Lv1.5 ~ Lv2の問題を解いてみる会 2023/9/7

DBアワー出張編 Lv1.5 ~ Lv2の問題を解いてみる会 2023/9/7

 

概要

前回：DBアワー出張編 Lv1 ~ Lv2の問題を解いてみる会 2023/8/17
今日は1.5、2の過去問を解きます
いきなりはむずいので必要な知識解説 + 演習、でいきます

録画データ

過去問

Lv1.5

2. 各肌質ごとのユーザ数の一覧

肌質ごとに、ユーザーの数を数えれば良い
第一回でやりました！やってみよう
count, sum, avg, group by

3. タイトルが「。」で終わる、または本文が「 」（半角スペース）で始まる記事の、個数とその平均閲覧数

個数と平均閲覧数は ↑ と同じく前回やった。問題は条件
  • タイトルが「。」で終わる
  • 本文が「 」（半角スペース）で始まる
→ どちらも文字列の一部の話！

LIKE

文字列の一部が一致していることを確認したいときに使う
https://docs.aws.amazon.com/ja_jp/redshift/latest/dg/r_patternmatching_condition_like.html
 
発展：もっと複雑な条件で一致しているものを探したい
→ 正規表現を使ったパターンマッチが可能
正規表現勉強会
https://docs.aws.amazon.com/ja_jp/redshift/latest/dg/pattern-matching-conditions.html

1. 商品が紐づいていない投稿の数

  • 商品が紐づいていない投稿を出す
    • その数を数える

    JOIN

    エクセル、スプレッドシート触ることある人なら VLOOKUP だと思ってもらえればOK
    JOIN ってなに？
     
    ONの中身が大事です！！
    ここを間違えると結果が大きく変わってくるため。ミスとしてもよくあるので注意しましょう
    結合すると増えるのも大事です！！
    後で説明します
     
    ユーザ名が yrmts であるユーザの投稿で紹介されている商品名の一覧

    LEFT JOINとNULL

    VLOOKUPで探しているものがない場合、エラーになったりする。そのときJOINは行を消す！
    LEFT JOINは残すが、データがないので空のまま → これがNULL
    NULLで絞ると空のものがわかる
    このとき は駄目で を使う。 逆は
    https://redash.appbrew.io/queries/12848/source

    4. 2019-06-02から2019-06-07の間に、お問い合わせを2回以上送ってきたユーザの、ユーザ名と問い合わせ回数の一覧（問い合わせ回数が多い順）

    お問い合わせを期間で絞って数える、のは前回やりました！
    ❓直近30日で、100回以上来た問い合わせ種別（category）
    違いは
    • between
    • お問い合わせを2回以上送ってきた
      • これはhavingを使えば良い
    • ユーザー名が必要で、ユーザーごとにまとめる
      • inquiriesにはuser_idしかない → usersをJOINして使う

    5. 100個以上クチコミが投稿されている商品について、そのクチコミを投稿している収益化ユーザのユーザ数

    ちょっと問題を改変して、ユーザー数からみてみましょう
    あとでユーザーid一覧も
     
    100個以上クチコミが投稿されている商品
    post_countで絞れる
    そのクチコミを投稿しているユーザー
    join post_products
    join posts
    join users
    そのうちの収益化ユーザー
    users monetization_status = 4

    JOINで増える話

    JOIN ってなに？ で説明されている商品と種類、順番を逆にしてもJOINの結果は同じです
    つまり、種類側から見ると行が増えている！

    count・distinct

    Lv2

    Q1

    存在する全商品の中でデパコス商品の割合を小数点で出す ここで言うデパコスとは、productsのprice_rangeが3の商品のことを指す。
    やり方2つあるが時間に余裕があったらもう一つも
     
    デパコス商品の数 / 全商品の数 = 割合

    subqueryとwith

    結果を使いまわしたいことがある → シンプルに括弧で囲んでおけば、テーブルと同じように扱える
    でも見づらい… → withを使うと良い！
    Lv1.5 の 5の別解

    float

    前回時刻のところでちょっと型の説明をしました！そこで小数ではfloatを使うという話をちらっと
    整数÷整数は整数になってしまう

    Q2

    投稿本文の中で「ブルベ」と書かれた投稿数を年代別に知りたい。 また、年代は番号ではなく「20代前半」「30代後半」など日本語で書き、年代が若い順に並び替える。age_type = 0 は未登録、何にも当てはまらないケースはnullにする。また、公開中の投稿のみに絞る。
    • 投稿テキストの中で「ブルベ」と書かれた投稿： like
    • 投稿数を年代別に：group by age_type、 年代はuserに入っているので、join users
    • 公開中の投稿のみ：where published_at ≤ get_date() — （+timezoneを考える）
    • 年代は番号ではなく「20代前半」「30代後半」など日本語で書き：CASE

    CASE

    条件によって値を変更したいときに使います！

    Q3（skip）

    投稿した画像（image）及び動画(movie)枚数が多いユーザーを知りたい。 ユーザーidごとに、画像・動画合わせた投稿枚数を出す。また、投稿枚数が100枚を超える（100は含まない）ユーザーのみを表示させる。投稿枚数が多い順に出す。 ※フルスクリーン動画、通常動画は分けなくてOK

    UNION

    Q4

    日本時間2021年8月1日-8月20日に登録したユーザーのうち、乾燥肌と混合肌のユーザーとそのユーザーの総投稿数を知りたい。下記の要素を含めてpost_count降順で表示せよ。またredashの機能を用い、IDからそれぞれのユーザープロフィールページへ遷移できるようリンクを貼る。
    • id
    • name
    • post_count
    • プロフィール画像（URLではなく、redashの機能を用いて一覧上で画像を見られるようにする）
    • users.created between
    • users.skin_type, in
      • 3: 乾燥肌
      • 4: 混合肌
    • ユーザーの総投稿数：users.post_count
    • post_count降順：order by

    IN

    = で複数指定したいときは IN！
     
    redashのvisualizeを紹介

    Q5（できなかった）

    PVが多いのに商品が紐付いていない投稿があったとき、もし紐付けるべき適切な商品があるなら紐付けたい。 そのため、商品が紐づいていない投稿を直近30日間の閲覧数の多い順に知りたい。 投稿IDと閲覧数がわかるようにし、PVが多い順に上位100件まで表示させる。また、投稿IDから投稿ページへ飛べるようにする。

    event_logs

    パラメータ機能