데이터 분석을 위한 SQL 레시피 - 5장 사용자를 파악하기 위한 데이터 추출 (1)

 1
 2
 3
 4
 5
 6
 7
 8
 9
10
11
12
13
14
15
16
17
18
19
20
21

WITH state as (
    -- 로그 전체의 유니크 사용자 수 구하기
    SELECT COUNT(DISTINCT session) AS total_uu
    FROM sample_data.action_log
)
SELECT
    l.action
  , COUNT(DISTINCT l.session) AS action_uu      -- 액션 UU
  , COUNT(1)                  AS action_count   -- 액션 수
  , s.total_uu                                  -- 전체 UU

  -- 사용률 : 액션 UU / 전체 UU
  , 100.0 * COUNT(DISTINCT l.session) / s.total_uu AS usage_rate

  -- 1인당 액션 수 : 액션 수 / 액션 UU
  , 1.0 * COUNT(1) / COUNT(DISTINCT l.session) AS count_per_user

FROM sample_data.action_log AS l
CROSS JOIN state AS s -- 로그 전체의 유니크 사용자 수를 모든 레코드에 결합하기
GROUP BY l.action, s.total_uu
;

 1
 2
 3
 4
 5
 6
 7
 8
 9
10
11

WITH action_log_with_status AS (
    SELECT
        session
      , user_id
      , action
        -- user_id 가 NULL 또는 빈 문자가 아닌 경우 login 이라 판정하기
      , CASE WHEN COALESCE(user_id, '') <> '' THEN 'login' ELSE 'guest' END AS login_status
    FROM sample_data.action_log
)
SELECT * FROM action_log_with_status
;

 1
 2
 3
 4
 5
 6
 7
 8
 9
10
11
12
13
14
15
16
17
18
19
20
21

WITH action_log_with_status AS (
    SELECT
        session
      , user_id
      , action
        -- user_id 가 NULL 또는 빈 문자가 아닌 경우 login 이라 판정하기
      , CASE WHEN COALESCE(user_id, '') <> '' THEN 'login' ELSE 'guest' END AS login_status
    FROM sample_data.action_log
)
SELECT
    COALESCE(action, 'all') AS action
  , login_status
  , COALESCE(login_status, 'all') AS login_status
  , COUNT(DISTINCT session) AS action_uu
  , COUNT(1) AS action_count
FROM action_log_with_status
-- PostgreSQL, SparkSQL
GROUP BY ROLLUP(action, login_status)
-- Hive 의 경우
-- action, login_status WITH ROLLUP
;

 1
 2
 3
 4
 5
 6
 7
 8
 9
10
11
12
13
14

WITH action_log_with_status AS (
    SELECT
        session
      , user_id
      , action
      -- 로그를 타임스탬프 순서로 나열하고, 한번이라도 로그인한 사용자일 경우 이후의 모든 로그 상태를 member로 설정
      , CASE WHEN COALESCE(MAX(user_id) OVER(PARTITION BY session ORDER BY stamp ROWS BETWEEN UNBOUNDED PRECEDING AND CURRENT ROW), '') <> '' THEN 'member'
        ELSE 'none'
        END AS member_status
      , stamp
    FROM sample_data.action_log
)
SELECT * FROM action_log_with_status
;

 1
 2
 3
 4
 5
 6
 7
 8
 9
10
11
12
13
14
15
16
17
18
19
20
21
22
23
24
25
26

WITH mst_users_with_int_birth_date AS (
    SELECT
        *
      -- 특정 날짜(2017년 1월 1일)의 정수 표현
      , 20170101 AS int_specific_date

      -- 문자열로 구성된 생년월일을 정수 표현으로 변환하기
      -- PostgreSQL, Redshift 의 경우 다음과 같이 작성
      --, CAST(replace(substring(birth_date, 1, 10), '-', '') AS int64) AS int_birth_date

      -- BigQuery 의 경우
      , CAST(replace(substr(birth_date, 1, 10), '-', '') AS int64) AS int_birth_date
      -- Hive, SparkSQL 의 경우
      --, CAST(regexp_replace(substring(birth_date, 1, 10), '-', '') AS int) AS int_birth_Date
    FROM sample_data.mst_users
),
mst_users_with_age AS (
    SELECT
        *
      , floor((int_specific_date - int_birth_date) / 10000) AS age
    FROM mst_users_with_int_birth_date
)
SELECT
    user_id, sex, birth_date, age
FROM mst_users_with_age
;

 1
 2
 3
 4
 5
 6
 7
 8
 9
10
11
12
13
14
15
16
17
18
19
20
21
22
23
24
25
26
27
28
29
30

WITH mst_users_with_int_birth_date AS (
    SELECT
        *
      , 20170101 AS int_specific_date
      , CAST(replace(substr(birth_date, 1, 10), '-', '') AS int64) AS int_birth_date
    FROM sample_data.mst_users
),
mst_users_with_age AS (
    SELECT
        *
      , floor((int_specific_date - int_birth_date) / 10000) AS age
    FROM mst_users_with_int_birth_date
),
mst_users_with_category AS (
    SELECT
        user_id
      , sex
      , age
      , CONCAT(CASE WHEN 20 <= age THEN sex ELSE '' END
              ,CASE
                 WHEN age BETWEEN 4 AND 12 THEN 'C'
                 WHEN age BETWEEN 13 AND 19 THEN 'T'
                 WHEN age BETWEEN 20 AND 34 THEN '1'
                 WHEN age BETWEEN 35 AND 49 THEN '2'
                 WHEN age >= 50 THEN '3'
               END) AS category
    FROM mst_users_with_age
)
SELECT * FROM mst_users_with_category
;

 1
 2
 3
 4
 5
 6
 7
 8
 9
10
11
12
13
14
15
16
17
18
19
20
21
22
23
24
25
26
27
28
29
30
31
32

WITH mst_users_with_int_birth_date AS (
    SELECT
        *
      , 20170101 AS int_specific_date
      , CAST(replace(substr(birth_date, 1, 10), '-', '') AS int64) AS int_birth_date
    FROM sample_data.mst_users
),
mst_users_with_age AS (
    SELECT
        *
      , floor((int_specific_date - int_birth_date) / 10000) AS age
    FROM mst_users_with_int_birth_date
),
mst_users_with_category AS (
    SELECT
        user_id
      , sex
      , age
      , CONCAT(CASE WHEN 20 <= age THEN sex ELSE '' END
              ,CASE
                 WHEN age BETWEEN 4 AND 12 THEN 'C'
                 WHEN age BETWEEN 13 AND 19 THEN 'T'
                 WHEN age BETWEEN 20 AND 34 THEN '1'
                 WHEN age BETWEEN 35 AND 49 THEN '2'
                 WHEN age >= 50 THEN '3'
               END) AS category
    FROM mst_users_with_age
)
SELECT category, COUNT(*) AS user_count
FROM mst_users_with_category
GROUP BY category
;

 1
 2
 3
 4
 5
 6
 7
 8
 9
10
11
12
13
14
15
16
17
18
19
20
21
22
23
24
25
26
27
28
29
30
31
32
33
34
35
36
37
38

WITH mst_users_with_int_birth_date AS (
    SELECT
        *
      , 20170101 AS int_specific_date
      , CAST(replace(substr(birth_date, 1, 10), '-', '') AS int64) AS int_birth_date
    FROM sample_data.mst_users
),
mst_users_with_age AS (
    SELECT
        *
      , floor((int_specific_date - int_birth_date) / 10000) AS age
    FROM mst_users_with_int_birth_date
),
mst_users_with_category AS (
    SELECT
        user_id
      , sex
      , age
      , CONCAT(CASE WHEN 20 <= age THEN sex ELSE '' END
              ,CASE
                 WHEN age BETWEEN 4 AND 12 THEN 'C'
                 WHEN age BETWEEN 13 AND 19 THEN 'T'
                 WHEN age BETWEEN 20 AND 34 THEN '1'
                 WHEN age BETWEEN 35 AND 49 THEN '2'
                 WHEN age >= 50 THEN '3'
               END) AS category
    FROM mst_users_with_age
)
SELECT
    p.category AS product_category
  , u.category AS user_category
  , COUNT(*)   AS purchase_count
FROM sample_data.action_log AS p
JOIN mst_users_with_category AS u ON p.user_id = u.user_id
WHERE p.action = 'purchase'
GROUP BY p.category, u.category
ORDER BY p.category, u.category
;

 1
 2
 3
 4
 5
 6
 7
 8
 9
10
11
12
13
14
15
16
17
18
19
20
21
22
23
24
25
26
27

WITH action_log_with_dt AS (
    SELECT
        *,

        -- 타임스탬프에서 날짜 추출하기
        -- PostgreSQL, Hive, Redshift, SparkSQL : substring 사용
        -- substring(stamp, 1, 10) AS dt
        -- PostgreSQL, Hive, BigQuery, SparkSQL : substr 사용
        substr(stamp, 1, 10) AS dt
    FROM sample_data.action_log
)
, action_day_count_per_user AS (
    SELECT
        user_id
      , COUNT(DISTINCT dt) AS action_day_count
    FROM action_log_with_dt
    -- 2016년 11월 1일부터 11월 7일까지의 한 주 동안을 대상으로 선정
    WHERE dt BETWEEN '2016-10-11' AND '2016-11-07'
    GROUP BY user_id
)
SELECT
    action_day_count
  , COUNT(DISTINCT user_id) AS user_count
FROM action_day_count_per_user
GROUP BY action_day_count
ORDER BY action_day_count
;

 1
 2
 3
 4
 5
 6
 7
 8
 9
10
11
12
13
14
15
16
17
18
19
20
21
22
23
24
25
26
27
28
29
30
31
32
33
34
35

WITH action_log_with_dt AS (
    SELECT
        *,

        -- 타임스탬프에서 날짜 추출하기
        -- PostgreSQL, Hive, Redshift, SparkSQL : substring 사용
        -- substring(stamp, 1, 10) AS dt
        -- PostgreSQL, Hive, BigQuery, SparkSQL : substr 사용
        substr(stamp, 1, 10) AS dt
    FROM sample_data.action_log
)
, action_day_count_per_user AS (
    SELECT
        user_id
      , COUNT(DISTINCT dt) AS action_day_count
    FROM action_log_with_dt
    -- 2016년 11월 1일부터 11월 7일까지의 한 주 동안을 대상으로 선정
    WHERE dt BETWEEN '2016-10-11' AND '2016-11-07'
    GROUP BY user_id
)
SELECT
    action_day_count
  , COUNT(DISTINCT user_id) AS user_count

  -- 구성비
  , 100.0 * COUNT(DISTINCT user_id) / SUM(COUNT(DISTINCT user_id)) OVER () as composition_ratio

  -- 구성비누계
  , 100.0 * SUM(COUNT(DISTINCT user_id)) OVER(ORDER BY action_day_count ROWS BETWEEN UNBOUNDED PRECEDING AND CURRENT ROW )
          / SUM(COUNT(DISTINCT user_id)) OVER() AS camulative_ratio

FROM action_day_count_per_user
GROUP BY action_day_count
ORDER BY action_day_count
;

 1
 2
 3
 4
 5
 6
 7
 8
 9
10
11
12
13
14
15
16
17
18
19
20

WITH user_action_flag AS (
    SELECT
        user_id
      , SIGN(SUM(CASE WHEN action = 'purchase' THEN 1 ELSE 0 END))  AS has_purchase
      , SIGN(SUM(CASE WHEN action = 'review' THEN 1 ELSE 0 END))    AS has_review
      , SIGN(SUM(CASE WHEN action = 'favorite' THEN 1 ELSE 0 END))  AS has_favorite
    FROM action_log
    GROUP BY user_id
), active_venn_diagram AS (
    SELECT
        has_purchase
      , has_review
      , has_favorite
      , COUNT(1) AS users
    FROM user_action_flag
    GROUP BY CUBE(has_purchase, has_review, has_favorite)
)
SELECT * FROM active_venn_diagram
ORDER BY has_purchase, has_review, has_favorite
;

 1
 2
 3
 4
 5
 6
 7
 8
 9
10
11
12
13
14
15
16
17
18
19
20
21

-- PostgreSQL
WITH user_action_flag AS (
    SELECT
        user_id
      , SIGN(SUM(CASE WHEN action = 'purchase' THEN 1 ELSE 0 END))  AS has_purchase
      , SIGN(SUM(CASE WHEN action = 'review' THEN 1 ELSE 0 END))    AS has_review
      , SIGN(SUM(CASE WHEN action = 'favorite' THEN 1 ELSE 0 END))  AS has_favorite
    FROM action_log
    GROUP BY user_id
), active_venn_diagram AS (
    SELECT
        has_purchase
      , has_review
      , has_favorite
      , COUNT(1) AS users
    FROM user_action_flag
    GROUP BY CUBE(has_purchase, has_review, has_favorite)
)
SELECT * FROM active_venn_diagram
ORDER BY has_purchase, has_review, has_favorite
;

 1
 2
 3
 4
 5
 6
 7
 8
 9
10
11
12
13
14
15
16
17
18
19
20
21
22
23
24
25
26
27
28
29
30
31
32
33
34
35
36
37
38
39
40
41
42
43
44
45
46
47

WITH user_action_flag AS (
    -- 사용자가 액션을 했으면 1, 안했으면 0
    SELECT
        user_id
      , SIGN(SUM(CASE WHEN action = 'purchase' THEN 1 ELSE 0 END))  AS has_purchase
      , SIGN(SUM(CASE WHEN action = 'review' THEN 1 ELSE 0 END))    AS has_review
      , SIGN(SUM(CASE WHEN action = 'favorite' THEN 1 ELSE 0 END))  AS has_favorite
    FROM sample_data.action_log
    GROUP BY user_id
),
action_venn_diagram AS (
    -- 모든 액션 조합을 개별적으로 구하고 UNION ALL로 결합

    -- 3개의 액션을 모두 한 경우 집계
    SELECT has_purchase, has_review, has_favorite, COUNT(*) AS users
    FROM user_action_flag GROUP BY has_purchase, has_review, has_favorite

    -- 3개의 액션 중 2개의 액션을 한 경우 집계
    UNION ALL
    SELECT NULL AS has_purchase, has_review, has_favorite, COUNT(*) AS users
    FROM user_action_flag GROUP BY has_review, has_favorite
    UNION ALL
    SELECT has_purchase, NULL AS has_review, has_favorite, COUNT(*) AS users
    FROM user_action_flag GROUP BY has_purchase, has_favorite
    UNION ALL
    SELECT has_purchase, has_review, NULL AS has_favorite, COUNT(*) AS users
    FROM user_action_flag GROUP BY has_purchase, has_review

    -- 3개의 액션 중 1개의 액션을 한 경우 집계
    UNION ALL
    SELECT NULL AS has_purchase, NULL AS has_review, has_favorite, COUNT(*) AS users
    FROM user_action_flag GROUP BY has_favorite
    UNION ALL
    SELECT NULL AS has_purchase, has_review, NULL AS has_favorite, COUNT(*) AS users
    FROM user_action_flag GROUP BY has_review
    UNION ALL
    SELECT has_purchase, NULL AS has_review, NULL AS has_favorite, COUNT(*) AS users
    FROM user_action_flag GROUP BY has_purchase

    -- 액션과 관계 없이 모든 사용자 집계
    UNION ALL
    SELECT NULL AS has_purchase, NULL AS has_review, NULL AS has_favorite, COUNT(*) AS users
    FROM user_action_flag
)
SELECT * FROM action_venn_diagram
ORDER BY has_purchase, has_review, has_favorite
;

 1
 2
 3
 4
 5
 6
 7
 8
 9
10
11
12
13
14
15
16
17
18
19
20
21
22
23
24
25
26
27
28
29
30

WITH user_action_flag AS (
    -- 사용자가 액션을 했으면 1, 안했으면 0
    SELECT
        user_id
      , SIGN(SUM(CASE WHEN action = 'purchase' THEN 1 ELSE 0 END))  AS has_purchase
      , SIGN(SUM(CASE WHEN action = 'review' THEN 1 ELSE 0 END))    AS has_review
      , SIGN(SUM(CASE WHEN action = 'favorite' THEN 1 ELSE 0 END))  AS has_favorite
    FROM sample_data.action_log
    GROUP BY user_id
)
, action_venn_diagram AS (
    SELECT
        mod_has_purchase
      , mod_has_review
      , mod_has_favorite
      , COUNT(1) AS users
    FROM user_action_flag
    -- 각 컬럼에 NULL을 포함하는 레코드를 유사적으로 추가하기
    -- BigQuery의 경우 CROSS JOIN unnest 함수 사용
    CROSS JOIN unnest(array[has_purchase, NULL]) AS mod_has_purchase
    CROSS JOIN unnest(array[has_review, NULL]) AS mod_has_review
    CROSS JOIN unnest(array[has_favorite, NULL]) AS mod_has_favorite

    -- Hive, SparkSQL의 경우 : LATERAL VIEW와 explode 함수 사용
    -- LATERAL VIEW explode(array(has_purchase, NULL)) e1 AS mode_has_purchase
    -- LATERAL VIEW explode(array(has_review, NULL)) e2 AS mod_has_review
    -- LATERAL VIEW explode(array(has_favorite, NULL)) e3 AS mod_has_favorite
    GROUP BY mod_has_purchase, mod_has_review, mod_has_favorite
) SELECT * FROM action_venn_diagram ORDER BY mod_has_purchase, mod_has_review, mod_has_favorite
;

 1
 2
 3
 4
 5
 6
 7
 8
 9
10
11
12
13
14
15
16
17
18
19
20
21
22
23
24
25
26
27
28
29
30
31
32
33
34

WITH user_action_flag AS (
    -- 사용자가 액션을 했으면 1, 안했으면 0
    SELECT
        user_id
      , SIGN(SUM(CASE WHEN action = 'purchase' THEN 1 ELSE 0 END))  AS has_purchase
      , SIGN(SUM(CASE WHEN action = 'review' THEN 1 ELSE 0 END))    AS has_review
      , SIGN(SUM(CASE WHEN action = 'favorite' THEN 1 ELSE 0 END))  AS has_favorite
    FROM sample_data.action_log
    GROUP BY user_id
)
, action_venn_diagram AS (
        SELECT
        mod_has_purchase
      , mod_has_review
      , mod_has_favorite
      , COUNT(1) AS users
    FROM user_action_flag
    CROSS JOIN unnest(array[has_purchase, NULL]) AS mod_has_purchase
    CROSS JOIN unnest(array[has_review, NULL]) AS mod_has_review
    CROSS JOIN unnest(array[has_favorite, NULL]) AS mod_has_favorite
    GROUP BY mod_has_purchase, mod_has_review, mod_has_favorite
)
SELECT
    -- 플래그 0, 1을 가공하기
    CASE mod_has_purchase WHEN 1 THEN 'purchase' WHEN 0 THEN 'not purchase' ELSE 'any' END AS has_purchase
  , CASE mod_has_review WHEN 1 THEN 'review' WHEN 0 THEN 'not review' ELSE 'any' END AS has_review
  , CASE mod_has_favorite WHEN 1 THEN 'favorite' WHEN 0 THEN 'not favorite' ELSE 'any' END AS has_favorite
  , users

  -- 전체 사용자 수를 기반으로 비율 구하기
  , 100.0 * users / NULLIF (SUM(CASE WHEN mod_has_purchase IS NULL AND mod_has_review IS NULL AND mod_has_favorite IS NULL THEN users ELSE 0 END) OVER(), 0) AS ratio
FROM action_venn_diagram
ORDER BY has_purchase, has_review, has_favorite
;

 1
 2
 3
 4
 5
 6
 7
 8
 9
10
11
12
13
14
15
16
17

WITH user_purchase_amount AS (
    SELECT
        user_id
      , SUM(amount) AS purchase_amount
    FROM sample_data.action_log
    WHERE action = 'purchase'
    GROUP BY user_id
),
user_with_decile AS (
    SELECT
        user_id
      , purchase_amount
      , ntile(10) OVER (ORDER BY purchase_amount DESC) AS decile
    FROM user_purchase_amount
)
SELECT * FROM user_with_decile
;

 1
 2
 3
 4
 5
 6
 7
 8
 9
10
11
12
13
14
15
16
17
18
19
20
21
22
23
24
25
26
27

WITH user_purchase_amount AS (
    SELECT
        user_id
      , SUM(amount) AS purchase_amount
    FROM sample_data.action_log
    WHERE action = 'purchase'
    GROUP BY user_id
),
user_with_decile AS (
    SELECT
        user_id
      , purchase_amount
      , ntile(10) OVER (ORDER BY purchase_amount DESC) AS decile
    FROM user_purchase_amount
),
decile_with_purchase_amount AS (
    SELECT
        decile
      , SUM(purchase_amount) AS amount
      , AVG(purchase_amount) AS avg_amount
      , SUM(SUM(purchase_amount)) OVER (ORDER BY decile) AS cumulative_amount
      , SUM(SUM(purchase_amount)) OVER () AS total_amount
    FROM user_with_decile GROUP BY decile
)
SELECT
*
FROM decile_with_purchase_amount;

 1
 2
 3
 4
 5
 6
 7
 8
 9
10
11
12
13
14
15
16
17
18
19
20
21
22
23
24
25
26
27
28
29
30
31

WITH user_purchase_amount AS (
    SELECT
        user_id
      , SUM(amount) AS purchase_amount
    FROM sample_data.action_log
    WHERE action = 'purchase'
    GROUP BY user_id
),
user_with_decile AS (
    SELECT
        user_id
      , purchase_amount
      , ntile(10) OVER (ORDER BY purchase_amount DESC) AS decile
    FROM user_purchase_amount
),
decile_with_purchase_amount AS (
    SELECT
        decile
      , SUM(purchase_amount) AS amount
      , AVG(purchase_amount) AS avg_amount
      , SUM(SUM(purchase_amount)) OVER (ORDER BY decile) AS cumulative_amount
      , SUM(SUM(purchase_amount)) OVER () AS total_amount
    FROM user_with_decile GROUP BY decile
)
SELECT
    decile
  , amount
  , avg_amount
  , 100.0 * amount / total_amount AS total_ratio
  , 100.0 * cumulative_amount / total_amount AS cumulative_ratio
FROM decile_with_purchase_amount;

 1
 2
 3
 4
 5
 6
 7
 8
 9
10
11
12
13
14
15
16
17
18
19
20
21
22
23
24
25
26
27
28
29
30
31
32

WITH purchase_log AS (
    SELECT
        user_id
      , amount

      -- PostgreSQL, Hive, BigQuery, SparkSQL 의 경우 substring 사용
      -- , substring(stamp, 1, 10) AS dt

      -- PostgreSQL, Hive, BigQuery, SparkSQL 의 경우 substr 사용
      , substr(stamp, 1, 10) AS dt
    FROM sample_data.action_log
    WHERE action = 'purchase'
)
, user_rfm AS (
    SELECT
        user_id
      , MAX(dt) AS recent_date

      -- BigQuery : date_diff 함수 사용
      , date_diff(CURRENT_DATE, date(timestamp(MAX(dt))), day) AS recency
      -- PostgreSQL, Redshift : 날짜 형식끼리 빼기 가능
      --, CURRENT_DATE - MAX(dt::date) AS recency
      -- Hive, SparkSQL : datediff 함수 사용
      --, datediff(CURRENT_DATE(), to_date(MAX(dt))) AS recency

      , COUNT(dt) AS frequency
      , SUM(amount) AS monetary
    FROM purchase_log
    GROUP BY user_id
) 
SELECT * FROM user_rfm
;

 1
 2
 3
 4
 5
 6
 7
 8
 9
10
11
12
13
14
15
16
17
18
19
20
21
22
23
24
25
26
27
28
29
30
31
32
33
34
35
36
37
38
39
40
41
42
43
44
45
46
47
48

WITH purchase_log AS (
    SELECT
        user_id
      , amount
      , substr(stamp, 1, 10) AS dt
    FROM sample_data.action_log
    WHERE action = 'purchase'
)
, user_rfm AS (
    SELECT
        user_id
      , MAX(dt) AS recent_date
      , date_diff(CURRENT_DATE, date(timestamp(MAX(dt))), day) AS recency
      , COUNT(dt) AS frequency
      , SUM(amount) AS monetary
    FROM purchase_log
    GROUP BY user_id
), user_rmf_rank AS (
    SELECT
        user_id
      , recent_date
      , recency
      , frequency
      , monetary
      , CASE
          WHEN recency < 14 THEN 5
          WHEN recency < 28 THEN 4
          WHEN recency < 60 THEN 3
          WHEN recency < 90 THEN 2
          ELSE 1
        END AS r
      , CASE
          WHEN 20 <= frequency THEN 5
          WHEN 10 <= frequency THEN 4
          WHEN 5 <= frequency THEN 3
          WHEN 2 <= frequency THEN 2
          ELSE 1
        END AS f
      , CASE
          WHEN 300000 <= monetary THEN 5
          WHEN 100000 <= monetary THEN 4
          WHEN 30000 <= monetary THEN 3
          WHEN 5000 <= monetary THEN 2
          ELSE 1
        END AS m
    FROM user_rfm
)
SELECT * FROM user_rmf_rank;