コミュニティの皆さん、こんにちは。
6か月間（last_seen_at）の期間を経てコミュニティに戻ってきたユーザーのリストを取得したいと考えています。
usersテーブルからlast_seen_atの日付を取得できることはわかっています。このタイムスタンプを比較し、6か月以上前かどうかを確認して、過去6か月間訪問していないユーザーを取得できますが、誰かがコミュニティを訪問すると、このタイムスタンプは現在の時刻に上書きされ、ユーザーの追跡が困難になります。
最後に訪問してから一定期間後にフォーラムに戻ってきたかどうかを確認することは可能でしょうか？
よろしくお願いします！
必要な詳細は user_visits テーブルにあります。私にとって難しいのは、各訪問エントリから前の訪問の値を差し引くことです。これにはウィンドウ関数が必要なようです。StackExchangeのこの質問への回答に基づくと、LEAD関数がこれに役立つようです。
このクエリを試して、探しているデータを返しているかどうかを確認してください。デフォルトでは、訪問間の期間が180日を超えたすべてのユーザー訪問が返されます。この値は、クエリの days_away パラメータを設定することで調整できます。
--[params]
-- integer :days_away = 180
WITH days_between_visits AS (
SELECT
user_id,
visited_at,
LEAD(visited_at) OVER (PARTITION BY user_id ORDER BY visited_at DESC) AS previous_visit,
visited_at - LEAD(visited_at) OVER (PARTITION BY user_id ORDER BY visited_at DESC) AS time_away
FROM user_visits
)
SELECT * FROM days_between_visits WHERE time_away >= :days_away
ORDER BY visited_at DESC
返されたデータに問題がないか、お知らせください。
いいですね。OPのニーズを満たすかどうかはわかりませんが、私にとっては \u003ex days で十分です。
素晴らしい、SQLができる皆さん、私はそんなトリックを学ぶのが面倒なのでここにいます😝
@simon さん、ありがとうございます。
私のユースケースにぴったりです 
あなたは真のSQLウィザードですね 
こんにちは、@simonさん
お手数をおかけして申し訳ありませんが、上記のSQLクエリでuser_idの代わりにusernameを取得することは可能でしょうか。data-explorerページではクエリ結果にユーザー名が表示されますが、結果をCSVファイルとしてエクスポートすると、ユーザー名がuser_idに置き換えられてしまいます。CSVファイルにも名前を含めたいのですが。
よろしくお願いします。
はい、こちらをお試しください：
--[params]
-- integer :days_away = 180
WITH days_between_visits AS (
SELECT
user_id,
visited_at,
LEAD(visited_at) OVER (PARTITION BY user_id ORDER BY visited_at DESC) AS previous_visit,
visited_at - LEAD(visited_at) OVER (PARTITION BY user_id ORDER BY visited_at DESC) AS time_away
FROM user_visits
)
SELECT
username,
dbv.user_id AS id,
visited_at,
time_away
FROM days_between_visits dbv
JOIN users u ON u.id = dbv.user_id
WHERE time_away >= :days_away
ORDER BY visited_at DESC
実行時にタイムアウトエラーが発生した場合はお知らせください。結果にはユーザーIDも引き続き含まれています。その列は現在idというラベルが付いています。IDを返したくない場合は、最後のSELECTステートメントからdbv.user_id AS id,行を削除してください。
どうもありがとうございます！
タイムアウトエラーは発生しませんでした 
This topic was automatically closed 30 days after the last reply. New replies are no longer allowed.