You can use get_current_user_id to get the id of the user currently viewing the page, and get_user_meta to get the site_id associated with that user. For example:
<?php
$user_id = get_current_user_id();
$site_id = get_user_meta($user_id, 'site_id', true);
Note: This does assume there is a user logged in, you might get unexpected results if there isn’t. So assuming you haven’t already done so, make sure the page can only be viewed by a logged in user.
To then use this site_id to get the scores per ‘site’, you can use a LEFT JOIN. The query you can use is:
$result = $wpdb->get_results("SELECT f_name, l_name, IF(tdata.score = tdata.maxscore, 'PASSED', 'FAILED') as score FROM wp_usermeta umeta LEFT JOIN wp_testing_data tdata ON tdata.user_id = umeta.user_id WHERE umeta.meta_key = 'site_id' AND umeta.meta_value="" . $site_id . """);
Do note that this will give you a query result for every user that has a site_id associated that matches $site_id, even if there’s no corresponding result in wp_testing_data. If you ONLY want those that have testing data, use INNER JOIN instead of LEFT JOIN.