VRCX.sqlite3 からインスタンスごとの再生動画情報を抽出する

vrcx-instance-videos.sql

SELECT
	gl.rowid AS join_id,
	gl.created_at AS world_created_at,
	gl.location AS instance_id,
	gl.world_id AS world_id,
	gl.world_name AS world_name,
	gl.time AS instance_elapsed_time,
	gl.group_name AS instance_group_name,
	gvp.created_at AS video_created_at,
	gvp.video_url AS video_url,
	gvp.video_name AS video_name,
	gvp.video_id AS video_platform,
	gvp.display_name AS video_play_user_display_name,
	gvp.user_id AS video_play_user_id
FROM
	gamelog_location gl
OUTER LEFT JOIN
	gamelog_video_play gvp 
ON
	gl.location = gvp.location 
	AND JULIANDAY(gl.created_at) <= JULIANDAY(gvp.created_at)
	AND JULIANDAY(gvp.created_at) < DATETIME(gl.created_at, '+' || (gl.time / 1000) || ' seconds')
ORDER BY gl.created_at DESC