Dataford
Interview QuestionsInterview GuidesExperiencesMock InterviewsPricing
Get started

Monthly Interaction Trends by Segment

MediumSQL · PostgreSQL00:00
Practice interviewer
In session
5 left
00:00

Your question is Monthly Interaction Trends by Segment. Start with the requirements and the three tables on the right.

Run and submit as often as you like. When you're ready, talk me through your approach or go straight to the code.

You need to log in / sign up to run or submit.

Problem

Write a PostgreSQL query to identify monthly customer interaction trends across Mölnlycke channels. For each month and customer segment, return total interactions, resolved interactions, resolution rate, and the most-used channel. Only keep month-segment groups with at least 2 interactions.

Schema

Tables
  • customers(customer_id, customer_name, segment, region)
  • interaction_channels(channel_id, channel_name)
  • customer_interactions(interaction_id, customer_id, channel_id, interaction_date, interaction_type, status, satisfaction_score)

What This Tests

  • Joining fact and dimension tables
  • Monthly aggregation with date formatting
  • Conditional aggregation using CASE WHEN
  • Using CTEs to isolate channel ranking logic
Tablescustomersinteraction_channelscustomer_interactions
Your solutionPostgreSQL
You need to log in / sign up to run or submit.
Run a query to see results