WordPress database error: [You have an error in your SQL syntax; check the manual that corresponds to your MySQL server version for the right syntax to use near ') -- Filter for results matching the given game_id(s) ), ' at line 12]
-- Step 1: Create a Common Table Expression (CTE) 'RankedResults' to rank each user's results by submit_time WITH RankedResults AS ( SELECT user_id, -- The ID of the user who submitted the result game_id, -- The ID of the game being played silver_coins, -- The number of silver coins earned in this submission submit_time, -- The timestamp when the result was submitted ROW_NUMBER() OVER (PARTITION BY user_id ORDER BY submit_time) AS rn -- Assign a unique rank to each result per user based on submission time FROM wp_construct3_results WHERE game_id IN() -- Filter for results matching the given game_id(s) ), -- Step 2: Select the first two submissions for each user FirstTwoRecords AS ( SELECT user_id, -- User ID game_id, -- Game ID silver_coins, -- Silver coins from the result submit_time -- Submission time FROM RankedResults WHERE rn <= 2 -- Only include the first two submissions per user ), -- Step 3: Calculate the time difference (in seconds) between the first and second submissions for each user TimeDifference AS ( SELECT user_id, -- User ID game_id, -- Game ID TIMESTAMPDIFF(SECOND, MIN(submit_time), MAX(submit_time)) AS time_diff_sec -- Time difference in seconds between the first and second submissions FROM FirstTwoRecords GROUP BY user_id, game_id -- Group by user and game to calculate time differences for each HAVING COUNT(submit_time) = 2 -- Ensure only users with exactly two submissions are included ) -- Step 4: Combine the results and calculate the leaderboard SELECT SUM(fr.silver_coins) AS points, -- Total points (sum of silver coins) for each user COALESCE(td.time_diff_sec, 0) AS time_taken_sec, -- Time taken for the first two submissions, or 0 if not applicable u.display_name, -- Display name of the user u.ID, -- User ID fr.game_id -- Game ID FROM FirstTwoRecords fr LEFT JOIN TimeDifference td ON fr.user_id = td.user_id AND fr.game_id = td.game_id -- Join with time differences calculated earlier JOIN wp_users u ON fr.user_id = u.ID -- Join with the users table to get user details GROUP BY fr.user_id, fr.game_id -- Group results by user and game ORDER BY points DESC, -- Sort primarily by points in descending order (highest points first) time_taken_sec ASC -- Sort secondarily by time taken in ascending order (lowest time first) LIMIT 3; -- Limit results for pagination

WordPress database error: [You have an error in your SQL syntax; check the manual that corresponds to your MySQL server version for the right syntax to use near ') -- Filter for results matching the given game_id(s) ), ' at line 12]
-- Step 1: Create a Common Table Expression (CTE) 'RankedResults' to rank each user's results by submit_time WITH RankedResults AS ( SELECT user_id, -- The ID of the user who submitted the result game_id, -- The ID of the game being played silver_coins, -- The number of silver coins earned in this submission submit_time, -- The timestamp when the result was submitted ROW_NUMBER() OVER (PARTITION BY user_id ORDER BY submit_time) AS rn -- Assign a unique rank to each result per user based on submission time FROM wp_construct3_results WHERE game_id IN() -- Filter for results matching the given game_id(s) ), -- Step 2: Select the first two submissions for each user FirstTwoRecords AS ( SELECT user_id, -- User ID game_id, -- Game ID silver_coins, -- Silver coins from the result submit_time -- Submission time FROM RankedResults WHERE rn <= 2 -- Only include the first two submissions per user ), -- Step 3: Calculate the time difference (in seconds) between the first and second submissions for each user TimeDifference AS ( SELECT user_id, -- User ID game_id, -- Game ID TIMESTAMPDIFF(SECOND, MIN(submit_time), MAX(submit_time)) AS time_diff_sec -- Time difference in seconds between the first and second submissions FROM FirstTwoRecords GROUP BY user_id, game_id -- Group by user and game to calculate time differences for each HAVING COUNT(submit_time) = 2 -- Ensure only users with exactly two submissions are included ) -- Step 4: Combine the results and calculate the leaderboard SELECT SUM(fr.silver_coins) AS points, -- Total points (sum of silver coins) for each user COALESCE(td.time_diff_sec, 0) AS time_taken_sec, -- Time taken for the first two submissions, or 0 if not applicable u.display_name, -- Display name of the user u.ID, -- User ID fr.game_id -- Game ID FROM FirstTwoRecords fr LEFT JOIN TimeDifference td ON fr.user_id = td.user_id AND fr.game_id = td.game_id -- Join with time differences calculated earlier JOIN wp_users u ON fr.user_id = u.ID -- Join with the users table to get user details GROUP BY fr.user_id, fr.game_id -- Group results by user and game ORDER BY points DESC, -- Sort primarily by points in descending order (highest points first) time_taken_sec ASC -- Sort secondarily by time taken in ascending order (lowest time first) LIMIT 3; -- Limit results for pagination