Analyze gym attendance You work as an analyst in a chain of gyms. You have information about user visits and subscriptions they purchase. You need to analyze the effectiveness of subscription usage. Calculate for each subscription type: • the total number of users who used this type of subscription. Count only unique user_id; • the total number of visits for this subscription. Count all visits of users with this subscription; • the share of users of this subscription in percentage of the total number of all users (rounded to one decimal place). To calculate the share, use the ratio of the number of users with this subscription to the total number of unique users. Each user can have only one subscription. Sort the result by subscription type in alphabetical order. Input format Memberships table: • membership_id (int) — unique subscription identifier • user_id (int) — unique user identifier • membership_type (text) — subscription type Visits table:

To analyze the attendance of fitness clubs by subscription types, you can use an SQL query that:

• Determines the total number of unique users for each subscription type.
• Counts the total visits for each subscription type.
• Calculates the share of users of each subscription type from the total number of unique users.
• Sorts the result by subscription type in alphabetical order.

Example query:

WITH user_counts AS (
  SELECT
    membership_type,
    COUNT(DISTINCT user_id) AS users_count
  FROM memberships
  GROUP BY membership_type
),

visit_counts AS (
  SELECT
    m.membership_type,
    COUNT(v.visit_id) AS visits_count
  FROM visits v
  JOIN memberships m ON v.user_id = m.user_id
  GROUP BY m.membership_type
),

total_users AS (
  SELECT COUNT(DISTINCT user_id) AS total_users FROM memberships
)

SELECT
  uc.membership_type,
  uc.users_count,
  COALESCE(vc.visits_count, 0) AS visits_count,
  ROUND( (uc.users_count::numeric / tu.total_users) * 100, 1) AS user_share_percent
FROM user_counts uc
LEFT JOIN visit_counts vc ON uc.membership_type = vc.membership_type
CROSS JOIN total_users tu
ORDER BY uc.membership_type;

Explanation:

• user_counts — number of unique users by subscription type.
• visit_counts — number of visits associated with each subscription type.
• total_users — total number of unique users.
• The final query combines the data and calculates the percentage share of users, rounded to one decimal place.

This approach will provide a comprehensive view of the effectiveness of each subscription type.