Featured image of post [R & SQL] データサイエンス100本ノック＋α - R-039 (解説)
データサイエンス100本ノック＋α

[R & SQL] データサイエンス100本ノック＋α - R-039 (解説)

売上上位顧客の完全外部結合

目次

参考記事:
[R & SQL] データサイエンス100本ノック＋α - 概要・導入

演習問題

難易度
レシート明細データ（receipt）から、売上日数の多い顧客の上位20件を抽出したデータと、売上金額合計の多い顧客の上位20件を抽出したデータをそれぞれ作成し、さらにその2つを完全外部結合せよ。ただし、非会員（顧客IDが"Z"から始まるもの）は除外すること。

出力イメージ:

   customer_id    n_date sum_amount
 1 CS040214000008     23         NA
 2 CS015415000185     22      20153
 3 CS010214000010     22      18585
 4 CS028415000007     21      19127
 5 CS010214000002     21         NA
 6 CS017415000097     20      23086
 7 CS016415000141     20      18372
 8 CS031414000051     19      19202
 9 CS014214000023     19         NA
10 CS021514000045     19         NA
...
32 CS007514000094     NA      15735
33 CS009414000059     NA      15492
34 CS030415000034     NA      15468
35 CS015515000034     NA      15300

実行環境の構築について

R コード実行環境の構築に必要な手順は こちら から確認できます。

セットアップが完了すると、必要なパッケージとデータを使用できるようになります。

以下に 2 通りの解答例を示します。
各コードの出力結果を統一させるため、n_date の降順、sum_amount の降順、customer_id の昇順でソートしています。

R (データフレーム操作)

利用するデータ

以下のデータを利用します。

  • df_receipt の顧客ID (customer_id)、売上年⽉⽇ (sales_ymd)、売上金額 (amount)

主要なカラムを抜き出してデータの一部を確認します。

R
df_receipt %>% select(customer_id, sales_ymd, amount)
# A tibble: 104,681 × 3
  customer_id    sales_ymd amount
  <chr>              <int>  <dbl>
1 CS006214000001  20181103    158
2 CS008415000097  20181118     81
3 CS028414000014  20170712    170
4 ZZ000000000000  20190205     25
5 CS025415000050  20180821     90
6 CS003515000195  20190605    138
7 CS024514000042  20181205     30
...

解答例 (1)

slice_max() を使用し、同じスコアの顧客を全て抽出する場合の解答例です。

解答コードと実行結果

R
df_rec = df_receipt %>% 
  filter(!str_detect(customer_id, "^Z")) %>% 
  select(customer_id, sales_ymd, amount) %>% 
  group_by(customer_id)

df_date = df_rec %>% 
  summarise(n_date = n_distinct(sales_ymd)) %>% 
  slice_max(n_date, n = 20, with_ties = TRUE)

df_amount = df_rec %>% 
  summarise(sum_amount = sum(amount)) %>% 
  slice_max(sum_amount, n = 20, with_ties = TRUE)

df_date %>% 
  full_join(df_amount, by = "customer_id") %>% 
  arrange(desc(n_date), desc(sum_amount), customer_id)
# A tibble: 35 × 3
   customer_id    n_date sum_amount
   <chr>           <int>      <dbl>
 1 CS040214000008     23         NA
 2 CS015415000185     22      20153
 3 CS010214000010     22      18585
 4 CS028415000007     21      19127
 5 CS010214000002     21         NA
 6 CS017415000097     20      23086
 7 CS016415000141     20      18372
 8 CS031414000051     19      19202
 9 CS014214000023     19         NA
10 CS021514000045     19         NA
11 CS021515000172     19         NA
12 CS022515000226     19         NA
13 CS039414000052     19         NA
14 CS007515000107     18         NA
15 CS014415000077     18         NA
16 CS021515000056     18         NA
17 CS021515000211     18         NA
18 CS022515000028     18         NA
19 CS030214000008     18         NA
20 CS031414000073     18         NA
21 CS032415000209     18         NA
22 CS001605000009     NA      18925
...
34 CS030415000034     NA      15468
35 CS015515000034     NA      15300

解説

df_rec
R
df_rec = df_receipt %>% 
  filter(!str_detect(customer_id, "^Z")) %>% 
  select(customer_id, sales_ymd, amount) %>% 
  group_by(customer_id)

ここでは、顧客データのフィルタリングとグループ化を行います。

  • df_receipt から、customer_id が "Z" で始まらないレコードを抽出します。
  • 必要な列 (customer_id、sales_ymd、amount) を選択し、customer_id でグループ化します。
df_date
R
df_date = df_rec %>% 
  summarise(n_date = n_distinct(sales_ymd)) %>% 
  slice_max(n_date, n = 20, with_ties = TRUE)

ここでは、顧客ごとの売上日数の集計と上位 20 件の抽出を行います。

  • df_rec から各顧客ごとの売上日数（n_date）を計算します。n_distinct(sales_ymd) は、ユニークな売上日をカウントします。
  • slice_max(n_date, n = 20, with_ties = TRUE) により、売上日数が多い上位 20 顧客を抽出します。 同順位に複数の顧客が該当すると、20 件を超える場合があります。
    出力結果から、n_date = 18 に該当する顧客が全て選択されたことにより、全体として 21 件分が抽出されたことが分かります。
df_amount
R
df_amount = df_rec %>% 
  summarise(sum_amount = sum(amount)) %>% 
  slice_max(sum_amount, n = 20, with_ties = TRUE)

ここでは、顧客ごとの総売上金額の集計と上位 20 件の抽出を行います。

  • df_rec から各顧客ごとの売上金額合計（sum_amount）を計算します。
  • slice_max(sum_amount, n = 20, with_ties = TRUE) により、売上金額が高い上位 20 顧客を抽出します。同順位に複数の顧客が該当すると、20 件を超える場合があります。
df_date と df_amount の統合およびソート
R
df_date %>% 
  full_join(df_amount, by = "customer_id") %>% 
  arrange(desc(n_date), desc(sum_amount), customer_id)
  • full_join() を使用して、df_date と df_amount を customer_id をキーとして完全外部結合します。これにより、売上日数と売上金額合計の各カラムを持つデータフレームが得られます。
  • 最後に、arrange() により、売上日数と売上金額合計の降順および customer_id の昇順でデータを並べ替えます。

解答例 (2)

arrange() + head(20) により、単純に上位 20 顧客ずつを抽出する場合の解答例です。
同スコアの一部の顧客が結果に含まれない場合もありますが、slice_max() を使う処理に比べて計算コストが低く、パフォーマンス面で有利になります。

解答コードと実行結果

R
df_rec = df_receipt %>% 
  filter(!str_detect(customer_id, "^Z")) %>% 
  select(customer_id, sales_ymd, amount) %>% 
  group_by(customer_id)

df_date = df_rec %>% 
  summarise(n_date = n_distinct(sales_ymd)) %>% 
  arrange(desc(n_date), customer_id) %>% 
  head(20)

df_amount = df_rec %>% 
  summarise(sum_amount = sum(amount)) %>% 
  arrange(desc(sum_amount), customer_id) %>% 
  head(20)

df_date %>% 
  full_join(df_amount, by = "customer_id") %>% 
  arrange(desc(n_date), desc(sum_amount), customer_id)
# A tibble: 34 × 3
   customer_id    n_date sum_amount
   <chr>           <int>      <dbl>
 1 CS040214000008     23         NA
 2 CS015415000185     22      20153
 3 CS010214000010     22      18585
 4 CS028415000007     21      19127
 5 CS010214000002     21         NA
 6 CS017415000097     20      23086
 7 CS016415000141     20      18372
 8 CS031414000051     19      19202
 9 CS014214000023     19         NA
10 CS021514000045     19         NA
11 CS021515000172     19         NA
12 CS022515000226     19         NA
13 CS039414000052     19         NA
14 CS007515000107     18         NA
15 CS014415000077     18         NA
16 CS021515000056     18         NA
17 CS021515000211     18         NA
18 CS022515000028     18         NA
19 CS030214000008     18         NA
20 CS031414000073     18         NA
21 CS001605000009     NA      18925
...
33 CS030415000034     NA      15468
34 CS015515000034     NA      15300

解説

解答例 (1) より、df_date と df_amount の処理を変更しています。

df_date
R
df_date = df_rec %>% 
  summarise(n_date = n_distinct(sales_ymd)) %>% 
  arrange(desc(n_date), customer_id) %>% 
  head(20)

ここでは、売上日数の上位 20 顧客を抽出します。

  • df_rec から顧客ごとの売上日数（n_date）をカウントします。
  • arrange(desc(n_date), customer_id) で売上日数の降順に並べ替え、head(20) で上位 20 顧客を抽出します。
df_amount
R
df_amount = df_rec %>% 
  summarise(sum_amount = sum(amount)) %>% 
  arrange(desc(sum_amount), customer_id) %>% 
  head(20)

ここでは、売上金額の上位 20 顧客を抽出します。

  • df_rec から顧客ごとの売上金額合計（sum_amount）を計算します。
  • arrange(desc(sum_amount), customer_id) で売上金額合計の降順に並べ替え、head(20) で上位 20 顧客を抽出します。

R (データベース操作)

解答例 (1)

データフレーム操作の 解答例 (1) をデータベース操作に適用した解答例です。

  • df_receipt をテーブル参照 db_receipt に置き換えます。
  • n_date = n_distinct(sales_ymd) %>% as.integer() により、売上日数を整数型に変換しています (デフォルトでは DOUBLE 型になるため)。
  • filter(!str_detect(...)) の代わりに %LIKE% を使用し、構文を簡潔にしています。
R
db_rec = db_receipt %>% 
  filter(!(customer_id %LIKE% "Z%")) %>% 
  select(customer_id, sales_ymd, amount) %>% 
  group_by(customer_id)

db_date = db_rec %>% 
  summarise(
    n_date = n_distinct(sales_ymd) %>% as.integer()
  ) %>% 
  slice_max(n_date, n = 20, with_ties = TRUE)

db_amount = db_rec %>% 
  summarise(sum_amount = sum(amount)) %>% 
  slice_max(sum_amount, n = 20, with_ties = TRUE)

db_result = db_date %>% 
  full_join(db_amount, by = "customer_id") %>% 
  arrange(desc(n_date), desc(sum_amount), customer_id)

db_result %>% collect()
# A tibble: 35 × 3
   customer_id    n_date sum_amount
   <chr>           <int>      <dbl>
 1 CS040214000008     23         NA
 2 CS015415000185     22      20153
 3 CS010214000010     22      18585
 4 CS028415000007     21      19127
 5 CS010214000002     21         NA
 6 CS017415000097     20      23086
 ...
  • %LIKE% は dbplyr が認識できないインフィックス関数なので、慣例に従い大文字で記述します。
    (詳しくは dbplyr が認識できない式 を参照してください。)
  • db_result %>% collect()
    データベース操作の結果を R のデータフレーム (tibble) として取得します。

解答例 (2)

同様に、データフレーム操作の 解答例 (2) をデータベース操作に適用した解答例です。

R
db_rec = db_receipt %>% 
  filter(!(customer_id %LIKE% "Z%")) %>% 
  select(customer_id, sales_ymd, amount) %>% 
  group_by(customer_id)

db_date = db_rec %>% 
  summarise(
    n_date = n_distinct(sales_ymd) %>% as.integer()
  ) %>% 
  arrange(desc(n_date), customer_id) %>% 
  head(20)

db_amount = db_rec %>% 
  summarise(sum_amount = sum(amount)) %>% 
  arrange(desc(sum_amount), customer_id) %>% 
  head(20)

db_result = db_date %>% 
  full_join(db_amount, by = "customer_id") %>% 
  arrange(desc(n_date), desc(sum_amount), customer_id)

db_result %>% collect()
# A tibble: 34 × 3
   customer_id    n_date sum_amount
   <chr>           <int>      <dbl>
 1 CS040214000008     23         NA
 2 CS015415000185     22      20153
 3 CS010214000010     22      18585
 4 CS028415000007     21      19127
 5 CS010214000002     21         NA
 6 CS017415000097     20      23086
 ...

SQL

利用するデータ

以下のデータを利用します。

  • receipt テーブルの顧客ID (customer_id)、売上年⽉⽇ (sales_ymd)、売上金額 (amount)

主要なカラムを抜き出してデータの一部を確認します。

R
"SELECT customer_id, sales_ymd, amount FROM receipt" %>% db_get_query(con)
# A tibble: 104,681 × 3
  customer_id    sales_ymd amount
  <chr>              <int>  <dbl>
1 CS006214000001  20181103    158
2 CS008415000097  20181118     81
3 CS028414000014  20170712    170
4 ZZ000000000000  20190205     25
5 CS025415000050  20180821     90
6 CS003515000195  20190605    138
7 CS024514000042  20181205     30
...

解答例 (1)

自動生成された SQL クエリ

データベース操作の 解答例 (1) による操作結果 (db_result) に基づき、自動生成された SQLクエリを show_query() で確認できます。

R
db_result %>% show_query(cte = TRUE)
SQL
WITH q01 AS (
  SELECT customer_id, sales_ymd, amount
  FROM receipt
  WHERE (NOT((customer_id LIKE 'Z%')))
),
q02 AS (
  SELECT customer_id, CAST(COUNT(DISTINCT row(sales_ymd)) AS INTEGER) AS n_date
  FROM q01
  GROUP BY customer_id
),
q03 AS (
  SELECT q01.*, RANK() OVER (ORDER BY n_date DESC) AS col01
  FROM q02 q01
),
q04 AS (
  SELECT customer_id, n_date
  FROM q03 q01
  WHERE (col01 <= 20)
),
q05 AS (
  SELECT customer_id, SUM(amount) AS sum_amount
  FROM q01
  GROUP BY customer_id
),
q06 AS (
  SELECT q01.*, RANK() OVER (ORDER BY sum_amount DESC) AS col02
  FROM q05 q01
),
q07 AS (
  SELECT customer_id, sum_amount
  FROM q06 q01
  WHERE (col02 <= 20)
),
q08 AS (
  SELECT
    COALESCE(LHS.customer_id, RHS.customer_id) AS customer_id,
    n_date,
    sum_amount
  FROM q04 LHS
  FULL JOIN q07 RHS
    ON (LHS.customer_id = RHS.customer_id)
)
SELECT q01.*
FROM q08 q01
ORDER BY n_date DESC, sum_amount DESC, customer_id

解答クエリ

このクエリをより簡潔な形に書き直すと、次のようになります。

SQL
WITH purchase_data AS (
  SELECT 
    customer_id, sales_ymd, amount
  FROM receipt
  WHERE customer_id NOT LIKE 'Z%'
),
customer_purchase_dates AS (
  SELECT 
    customer_id, 
    CAST(COUNT(DISTINCT sales_ymd) AS INTEGER) AS n_date
  FROM purchase_data
  GROUP BY customer_id
),
ranked_purchase_dates AS (
  SELECT 
    customer_id, 
    n_date, 
    RANK() OVER (ORDER BY n_date DESC) AS rank_n_date
  FROM customer_purchase_dates
),
customer_total_sales AS (
  SELECT 
    customer_id, 
    SUM(amount) AS sum_amount
  FROM purchase_data
  GROUP BY customer_id
),
ranked_total_sales AS (
  SELECT 
    customer_id, 
    sum_amount, 
    RANK() OVER (ORDER BY sum_amount DESC) AS rank_sum_amount
  FROM customer_total_sales
),
top_customers_by_dates AS (
  SELECT customer_id, n_date
  FROM ranked_purchase_dates
  WHERE rank_n_date <= 20
),
top_customers_by_sales AS (
  SELECT customer_id, sum_amount
  FROM ranked_total_sales
  WHERE rank_sum_amount <= 20
)
SELECT 
  COALESCE(d.customer_id, s.customer_id) AS customer_id,
  d.n_date,
  s.sum_amount
FROM top_customers_by_dates d
FULL JOIN top_customers_by_sales s
USING (customer_id) 
ORDER BY n_date DESC, sum_amount DESC, customer_id

解説

purchase_data の作成
SQL
WITH purchase_data AS (
 SELECT 
   customer_id, sales_ymd, amount
 FROM receipt
 WHERE customer_id NOT LIKE 'Z%'
)
  • receipt テーブルから、customer_id が 'Z' で始まる顧客を除外したデータを取得します。
  • customer_id、sales_ymd、amount の3つのカラムを選択します。
customer_purchase_dates の作成
SQL
customer_purchase_dates AS (
 SELECT 
   customer_id, 
   CAST(COUNT(DISTINCT sales_ymd) AS INTEGER) AS n_date
 FROM purchase_data
 GROUP BY customer_id
)
  • purchase_data から、COUNT(DISTINCT sales_ymd) により、ユニークな売上日をカウントします。
  • GROUP BY customer_id で、顧客ごとに集計を行います。
ranked_purchase_dates の作成
SQL
ranked_purchase_dates AS (
 SELECT 
   customer_id, 
   n_date, 
   RANK() OVER (ORDER BY n_date DESC) AS rank_n_date
 FROM customer_purchase_dates
)
  • customer_purchase_dates に対し RANK() OVER(...) ウィンドウ関数を使用して、売上日数 n_date に基づいてランク付けを行います。売上日数が多い顧客に低いランク (1位〜) を付けます。
  • 売上日数が同じ顧客には同じランクが付けられます。
customer_total_sales の作成
SQL
customer_total_sales AS (
 SELECT 
   customer_id, 
   SUM(amount) AS sum_amount
 FROM purchase_data
 GROUP BY customer_id
)
  • purchase_data から売上金額合計 (SUM(amount)) を計算します。
  • GROUP BY customer_id で、顧客ごとに集計します。
ranked_total_sales の作成
SQL
ranked_total_sales AS (
 SELECT 
   customer_id, 
   sum_amount, 
   RANK() OVER (ORDER BY sum_amount DESC) AS rank_sum_amount
 FROM customer_total_sales
)
  • customer_total_sales に対し RANK() OVER(...) ウィンドウ関数を使用して、 売上金額 sum_amount に基づいてランク付けを行います。売上金額が多い顧客に低いランク (1位〜) を付けます。
  • 売上金額が同じ顧客には同じランクが付けられます。
top_customers_by_dates の作成
SQL
top_customers_by_dates AS (
  SELECT customer_id, n_date
  FROM ranked_purchase_dates
  WHERE rank_n_date <= 20
)
  • ranked_purchase_dates から、売上日数の上位 20 顧客 (rank_n_date <= 20) を抽出します。
top_customers_by_sales の作成
SQL
top_customers_by_sales AS (
  SELECT customer_id, sum_amount
  FROM ranked_total_sales
  WHERE rank_sum_amount <= 20
)
  • ranked_total_sales から、売上金額の上位 20 顧客（rank_sum_amount <= 20）を抽出します。
メインクエリ
SQL
SELECT 
  COALESCE(d.customer_id, s.customer_id) AS customer_id,
  d.n_date,
  s.sum_amount
FROM top_customers_by_dates d
FULL JOIN top_customers_by_sales s
USING (customer_id) 
ORDER BY n_date DESC, sum_amount DESC, customer_id
  • top_customers_by_dates と top_customers_by_sales を customer_id をキーとして FULL JOIN で結合します。
  • COALESCE(d.customer_id, s.customer_id) により、片方のテーブルにしか存在しない customer_id を適切に処理します。
  • ORDER BY により、売上日数 (n_date) の降順、売上金額 (sum_amount) の降順、customer_id の昇順で並べ替えます。

実行結果の確認

この SQLクエリの実行結果は、次のようにして確認できます。

R
query = sql("
WITH purchase_data AS (
  SELECT 
    customer_id, sales_ymd, amount
  FROM receipt
  WHERE customer_id NOT LIKE 'Z%'
),
customer_purchase_dates AS (
  SELECT 
    customer_id, 
    CAST(COUNT(DISTINCT sales_ymd) AS INTEGER) AS n_date
  FROM purchase_data
  GROUP BY customer_id
),
ranked_purchase_dates AS (
  SELECT 
    customer_id, 
    n_date, 
    RANK() OVER (ORDER BY n_date DESC) AS rank_n_date
  FROM customer_purchase_dates
),
customer_total_sales AS (
  SELECT 
    customer_id, 
    SUM(amount) AS sum_amount
  FROM purchase_data
  GROUP BY customer_id
),
ranked_total_sales AS (
  SELECT 
    customer_id, 
    sum_amount, 
    RANK() OVER (ORDER BY sum_amount DESC) AS rank_sum_amount
  FROM customer_total_sales
),
top_customers_by_dates AS (
  SELECT customer_id, n_date
  FROM ranked_purchase_dates
  WHERE rank_n_date <= 20
),
top_customers_by_sales AS (
  SELECT customer_id, sum_amount
  FROM ranked_total_sales
  WHERE rank_sum_amount <= 20
)
SELECT 
  COALESCE(d.customer_id, s.customer_id) AS customer_id,
  d.n_date,
  s.sum_amount
FROM top_customers_by_dates d
FULL JOIN top_customers_by_sales s
USING (customer_id) 
ORDER BY n_date DESC, sum_amount DESC, customer_id
"
)

query %>% db_get_query(con)
# A tibble: 35 × 3
   customer_id    n_date sum_amount
   <chr>           <int>      <dbl>
 1 CS040214000008     23         NA
 2 CS015415000185     22      20153
 3 CS010214000010     22      18585
 4 CS028415000007     21      19127
 5 CS010214000002     21         NA
 6 CS017415000097     20      23086
 ...

解答例 (2)

自動生成された SQL クエリ

データベース操作の 解答例 (2) による操作結果 (db_result) に基づき、自動生成された SQLクエリを show_query() で確認できます。

R
db_result %>% show_query(cte = TRUE)
SQL
WITH q01 AS (
  SELECT customer_id, sales_ymd, amount
  FROM receipt
  WHERE (NOT((customer_id LIKE 'Z%')))
),
q02 AS (
  SELECT customer_id, CAST(COUNT(DISTINCT row(sales_ymd)) AS INTEGER) AS n_date
  FROM q01
  GROUP BY customer_id
  ORDER BY n_date DESC, customer_id
  LIMIT 20
),
q03 AS (
  SELECT customer_id, SUM(amount) AS sum_amount
  FROM q01
  GROUP BY customer_id
  ORDER BY sum_amount DESC, customer_id
  LIMIT 20
),
q04 AS (
  SELECT
    COALESCE(LHS.customer_id, RHS.customer_id) AS customer_id,
    n_date,
    sum_amount
  FROM q02 LHS
  FULL JOIN q03 RHS
    ON (LHS.customer_id = RHS.customer_id)
)
SELECT q01.*
FROM q04 q01
ORDER BY n_date DESC, sum_amount DESC, customer_id

解答クエリ

このクエリをより簡潔な形に書き直すと、次のようになります。

SQL
WITH purchase_data AS (
  SELECT 
    customer_id, 
    sales_ymd, 
    amount 
  FROM 
    receipt 
  WHERE 
    customer_id NOT LIKE 'Z%'
), 
top_customers_by_days AS (
  SELECT 
    customer_id, 
    CAST(COUNT(DISTINCT sales_ymd) AS INTEGER) AS n_date 
  FROM 
    purchase_data 
  GROUP BY 
    customer_id 
  ORDER BY 
    n_date DESC, customer_id 
  LIMIT 20
), 
top_customers_by_sales AS (
  SELECT 
    customer_id, 
    SUM(amount) AS sum_amount 
  FROM 
    purchase_data 
  GROUP BY 
    customer_id 
  ORDER BY 
    sum_amount DESC, customer_id 
  LIMIT 20
) 
SELECT 
  COALESCE(d.customer_id, s.customer_id) AS customer_id, 
  d.n_date, 
  s.sum_amount 
FROM 
  top_customers_by_days d
FULL JOIN 
  top_customers_by_sales s 
USING (customer_id) 
ORDER BY 
  n_date DESC, sum_amount DESC, customer_id

解説

解答例 (1)のクエリ との相違点は、top_customers_by_dates と top_customers_by_sales の作成方法です。

  • top_customers_by_dates の作成

    • 顧客ごとにユニークな売上日 (n_date) をカウントします。
    • n_date の降順で並べ替え、上位 20 人の顧客を LIMIT 20 で抽出します。

  • top_customers_by_sales の作成

    • 顧客ごとに売上金額合計 (sum_amount) を集計します。
    • sum_amount の降順で並べ替え、上位 20 人の顧客を LIMIT 20 で抽出します。

実行結果の確認

この SQLクエリの実行結果は、次のようにして確認できます。

R
query = sql("
WITH purchase_data AS (
  SELECT 
    customer_id, 
    sales_ymd, 
    amount 
  FROM 
    receipt 
  WHERE 
    customer_id NOT LIKE 'Z%'
), 
top_customers_by_dates AS (
  SELECT 
    customer_id, 
    CAST(COUNT(DISTINCT sales_ymd) AS INTEGER) AS n_date 
  FROM 
    purchase_data 
  GROUP BY 
    customer_id 
  ORDER BY 
    n_date DESC, customer_id 
  LIMIT 20
), 
top_customers_by_sales AS (
  SELECT 
    customer_id, 
    SUM(amount) AS sum_amount 
  FROM 
    purchase_data 
  GROUP BY 
    customer_id 
  ORDER BY 
    sum_amount DESC, customer_id 
  LIMIT 20
) 
SELECT 
  COALESCE(d.customer_id, s.customer_id) AS customer_id, 
  d.n_date, 
  s.sum_amount 
FROM 
  top_customers_by_dates d
FULL JOIN 
  top_customers_by_sales s 
USING (customer_id) 
ORDER BY 
  n_date DESC, sum_amount DESC, customer_id
"
)

query %>% db_get_query(con)
# A tibble: 34 × 3
   customer_id    n_date sum_amount
   <chr>           <int>      <dbl>
 1 CS040214000008     23         NA
 2 CS015415000185     22      20153
 3 CS010214000010     22      18585
 4 CS028415000007     21      19127
 5 CS010214000002     21         NA
 6 CS017415000097     20      23086
 ...