Below 𝐈’𝐦 𝐬𝐡𝐚𝐫𝐢𝐧𝐠 𝟓 𝐒𝐐𝐋 𝐈𝐧𝐭𝐞𝐫𝐯𝐢𝐞𝐰 𝐐𝐮𝐞𝐬𝐭𝐢𝐨𝐧𝐬 asked in a recent interview for a 𝐃𝐚𝐭𝐚 𝐀𝐧𝐚𝐥𝐲𝐬𝐭 𝐫𝐨𝐥𝐞 𝐚𝐭 𝐆𝐞𝐧𝐩𝐚𝐜𝐭 (𝟐–𝟓 𝐲𝐞𝐚𝐫𝐬 𝐞𝐱𝐩𝐞𝐫𝐢𝐞𝐧𝐜𝐞):-

🔹 Q1. Identify Active Subscriptions in a Given Timeframe
Table: subscriptions(subscription_id, activation_date, deactivation_date)
Find all subscription_ids that were active at any point between '2010-01-01' and '2020-12-31'.
(Note: If deactivation_date is NULL, consider it as still active.)

🔹 Q2. Group Continuous Membership Records into Start & End Periods
Table: memberships(user_id, plan_id, valid_till)
This table contains monthly records for users.

Group rows into blocks where a user stayed continuously subscribed to the same plan without missing a month, and return:
user_id, plan_id, start_date, end_date for each such block.

🔹 Q3. Count Logins Based on User Status & Lookback Window
Tables: users(user_id, is_active, offboarded_on)
logins(login_id, user_id, login_date)

For each user:
→ If is_active = true, count logins in the last 12 months from '2023-12-01'
→ If is_active = false, count logins in the 12 months before offboarded_on
Return: user_id, status, period_start, period_end, login_count

🔹 Q4. Match First Interaction Channel with Final Conversion
Tables: touchpoints(user_id, channel, interaction_date)
conversions(user_id, converted_on, is_converted)

Among users who converted, find how many had their first interaction channel same as the one that led to conversion.
Use min & max interaction dates to identify first and last channels. Return the percentage of such users.

🔹 Q5. Find Cross-Category Spenders in Last 6 Months
Tables:
purchases(user_id, product_id, purchase_date)
products(product_id, category)
Find users who bought products from at least 3 different categories in the last 6 months.
Return user_id and count of unique categories.

Tap ❤️ for more