SQL problem to solve (window function)
2024
Source for problem:https://www.interviewquery.com/questions/user-experience-percentage?via=venkata&ref=venkata
my solution :
WITH user_data AS (
SELECT
ux.position_name,
ux.user_id,
LAG(ux.position_name, 1) OVER (PARTITION BY ux.user_id) AS previous_position
FROM
user_experiences ux
),
user_ttoal as (
SELECT count(distinct ux3.user_id) as total
FROM user_experiences ux3
),
validate_data as (
SELECT us.user_id
from user_data us
WHERE us.previous_position = 'Data Analyst'
AND us.position_name = 'Data Scientist'
)
SELECT
(count(distinct v.user_id) / (SELECT total from user_ttoal) ) AS percentage
FROM
validate_data v
