基于COUNT、DISTINCT、CASE WHEN、LEFT OUTER JOIN的双表SQL查询需求

Got it! Let's build a practical SQL query that hits all your required clauses/functions while solving a meaningful business problem. First, let's recap our table structures clearly:

Users Table #

Sessions Table #

Let's say we want to calculate company-level user and session metrics: total unique users, users who have at least one session, session counts broken down by user agent, and total sessions per company. Here's the query that uses all your required components:

SELECT
    u.company,
    COUNT(DISTINCT u.user_id) AS total_unique_users,
    COUNT(DISTINCT CASE WHEN s.session_id IS NOT NULL THEN u.user_id END) AS users_with_active_sessions,
    COUNT(CASE WHEN s.user_agent = 'X' THEN s.session_id END) AS session_count_agent_x,
    COUNT(CASE WHEN s.user_agent = 'Y' THEN s.session_id END) AS session_count_agent_y,
    COUNT(s.session_id) AS total_sessions_per_company
FROM
    users u
LEFT OUTER JOIN
    sessions s ON u.user_id = s.user_id
GROUP BY
    u.company;

Breakdown of the required components: #

• LEFT OUTER JOIN: Ensures we keep every user from the users table, even if they have no matching sessions (like Microsoft's user ID 2). This prevents us from excluding users with zero activity.
• COUNT(DISTINCT): Used in total_unique_users to count each user only once, even if they have multiple sessions. We also use it in users_with_active_sessions to avoid counting the same user multiple times for their multiple sessions.
• CASE WHEN: Lets us apply conditional logic to our counts. For example, session_count_agent_x only counts sessions where the user agent is "X", and users_with_active_sessions only counts users who have at least one session record.
• COUNT: Works alongside the above to aggregate our conditional and non-conditional metrics.

Expected Query Result #

内容的提问来源于stack exchange，提问作者tshaw