Question: License Utilization
"Given tables for assigned licenses and active user logs, calculate what percentage of software licenses are actually being used."

Step-by-Step Explanation:

You have two tables:

1. assigned_licenses
   • user_id
   • license_id
   • product_name
2. active_user_logs
   • user_id
   • activity_time (timestamp when the user logged in or used the software)

You need to:

1. Calculate how many assigned licenses exist.
2. Calculate how many licenses are actually being used (based on users who have logged in).
3. Calculate the percentage of licenses being used.

Sample Data:

assigned_licenses

active_user_logs

Step 1: Count Total Assigned Licenses

Simply count the number of rows in assigned_licenses.

Example:

• Total assigned licenses = 4

Step 2: Find Active Users Who Have a License

We are only interested in users who:

• Are assigned a license (assigned_licenses)
• Have an activity in active_user_logs.

Join assigned_licenses with active_user_logs on user_id.

Use DISTINCT users, because a user might have logged in multiple times.

Example:

• User 1 (logged in) → Active
• User 3 (logged in) → Active

Total active users = 2

Step 3: Calculate Utilization Percentage

Utilization (%) = (Active Licenses / Assigned Licenses) × 100

Example:

• (2 / 4) × 100 = 50%

SQL Query:

WITH total_licenses AS (
  SELECT COUNT(*) AS assigned_license_count
  FROM assigned_licenses
),

active_licenses AS (
  SELECT COUNT(DISTINCT al.user_id) AS active_license_count
  FROM assigned_licenses al
  JOIN active_user_logs au
    ON al.user_id = au.user_id
)

SELECT
  al.active_license_count,
  tl.assigned_license_count,
  ROUND((al.active_license_count * 100.0 / tl.assigned_license_count), 2) AS license_utilization_percentage
FROM
  active_licenses al
CROSS JOIN
  total_licenses tl;

Explanation of SQL:

• total_licenses CTE:
  • Counts how many licenses have been assigned.
• active_licenses CTE:
  • Counts how many licensed users actually used their license (distinct user_ids).
• Final query:
  • Joins both results and calculates the utilization percentage.

Expected Output:

Important Points:

• Use DISTINCT on user_id because users can have multiple activities.
• Joining assigned licenses with active logs ensures we only count users who actually have a license and used it.
• CROSS JOIN is used because you want to combine two counts, not based on matching rows.
• ROUND(..., 2) gives the percentage rounded to 2 decimal places.