Test 3 - Quarry

SQL

USE ckbwiki_p;
-- Assuming user_id, up_user, up_property, and up_value are the relevant columns
-- Add indexes to these columns if not already done
CREATE INDEX idx_user_user_id ON user (user_id);
CREATE INDEX idx_user_properties_up_user ON user_properties (up_user);
CREATE INDEX idx_user_properties_up_property_value ON user_properties (up_property, up_value);

SELECT
    user_count,
    COALESCE(male_count, 0) AS male_count,
    COALESCE(female_count, 0) AS female_count,
    user_count - COALESCE(male_count, 0) - COALESCE(female_count, 0) AS remaining_count
FROM (
    SELECT
        (SELECT COUNT(DISTINCT user_id) FROM user) AS user_count,
        (SELECT COUNT(DISTINCT up_user) FROM user_properties WHERE up_property = 'gender' AND up_value = 'male') AS male_count,
        (SELECT COUNT(DISTINCT up_user) FROM user_properties WHERE up_property = 'gender' AND up_value = 'female') AS female_count
) AS counts;
SQL

Checking query status...
