Interview Guides

Pivot Driver Support Ticket Status | Dataford Interview Questions - Dataford - Ace your Interview

Pivot Driver Support Ticket Status

Easy

SQL & Data Manipulation

Asked at 1 company1Case WhenPivotData Wrangling

Also asked at

Problem

Task

Lyft's operations team often recreates Excel-style pivot tables to monitor support workload. Write a SQL query to summarize the number of driver support tickets by queue, with separate counts for each ticket status.

Requirements

Return one row per queue_name.
Count how many tickets in each queue are open, resolved, and escalated using conditional aggregation.
Exclude rows where queue_name is NULL.
Order the result by open_tickets descending, then queue_name ascending.

Table Definition

driver_support_tickets

column	type	description
ticket_id	INT	Unique ticket identifier
queue_name	VARCHAR(100)	Lyft support queue handling the ticket
ticket_status	VARCHAR(20)	Current status of the ticket
city	VARCHAR(100)	City tied to the issue
created_date	DATE	Date the ticket was created

Sample Data

ticket_id	queue_name	ticket_status	city	created_date
101	Driver Onboarding	open	Chicago	2024-06-03
102	Safety Escalations	escalated	San Francisco	2024-06-01
103	Driver Onboarding	resolved	Atlanta	2024-06-02
104	Earnings Review	open	Seattle	2024-06-04
105	Earnings Review	resolved	Chicago	2024-06-01
106	Driver Onboarding	open	Miami	2024-06-05

Expected Output

queue_name	open_tickets	resolved_tickets	escalated_tickets
Driver Onboarding	3	1	0
Earnings Review	2	1	1
Safety Escalations	1	1	2
Background Check	0	1	0

Schema

driver_support_tickets

Column	Type	Description
ticket_idPK	INT	Unique ticket identifier
queue_name	VARCHAR(100)	Lyft support queue handling the ticket
ticket_status	VARCHAR(20)	Current ticket status
city	VARCHAR(100)	City associated with the ticket
created_date	DATE	Date the ticket was created

Sample Data

Example 1

Input

{"driver_support_tickets":[["101","Driver Onboarding","open","Chicago","2024-06-03"],["102","Safety Escalations","escalated","San Francisco","2024-06-01"],["103","Driver Onboarding","resolved","Atlanta","2024-06-02"],["104","Earnings Review","open","Seattle","2024-06-04"],["105","Earnings Review","resolved","Chicago","2024-06-01"],["106","Driver Onboarding","open","Miami","2024-06-05"],["107","Safety Escalations","open","Boston","2024-06-06"],["108","Safety Escalations","resolved","Los Angeles",

Output

[["Driver Onboarding","3","1","0"],["Earnings Review","2","1","1"],["Safety Escalations","1","1","2"],["Airport Operations","0","0","0"],["Background Check","0","1","0"],["Driver Reactivation","0","0","0"],["Driver Referrals","0","0","0"],["Express Drive","0","0","0"],["Fraud Review","0","0","0"],["Inspection Support","0","0","0"],["Phone Verification","0","0","0"],["Region Transfers","0","0","0"],["Rental Support","0","0","0"],["Tax Forms","0","0","0"],["Vehicle Documents","0","0","0"]]

Problem

Task

Requirements

Return one row per queue_name.
Count how many tickets in each queue are open, resolved, and escalated using conditional aggregation.
Exclude rows where queue_name is NULL.
Order the result by open_tickets descending, then queue_name ascending.

Table Definition

driver_support_tickets

column	type	description
ticket_id	INT	Unique ticket identifier
queue_name	VARCHAR(100)	Lyft support queue handling the ticket
ticket_status	VARCHAR(20)	Current status of the ticket
city	VARCHAR(100)	City tied to the issue
created_date	DATE	Date the ticket was created

Sample Data

ticket_id	queue_name	ticket_status	city	created_date
101	Driver Onboarding	open	Chicago	2024-06-03
102	Safety Escalations	escalated	San Francisco	2024-06-01
103	Driver Onboarding	resolved	Atlanta	2024-06-02
104	Earnings Review	open	Seattle	2024-06-04
105	Earnings Review	resolved	Chicago	2024-06-01
106	Driver Onboarding	open	Miami	2024-06-05

Expected Output

queue_name	open_tickets	resolved_tickets	escalated_tickets
Driver Onboarding	3	1	0
Earnings Review	2	1	1
Safety Escalations	1	1	2
Background Check	0	1	0

Schema

driver_support_tickets

Column	Type	Description
ticket_idPK	INT	Unique ticket identifier
queue_name	VARCHAR(100)	Lyft support queue handling the ticket
ticket_status	VARCHAR(20)	Current ticket status
city	VARCHAR(100)	City associated with the ticket
created_date	DATE	Date the ticket was created

Sample Data

Example 1

Input

{"driver_support_tickets":[["101","Driver Onboarding","open","Chicago","2024-06-03"],["102","Safety Escalations","escalated","San Francisco","2024-06-01"],["103","Driver Onboarding","resolved","Atlanta","2024-06-02"],["104","Earnings Review","open","Seattle","2024-06-04"],["105","Earnings Review","resolved","Chicago","2024-06-01"],["106","Driver Onboarding","open","Miami","2024-06-05"],["107","Safety Escalations","open","Boston","2024-06-06"],["108","Safety Escalations","resolved","Los Angeles",

Output

[["Driver Onboarding","3","1","0"],["Earnings Review","2","1","1"],["Safety Escalations","1","1","2"],["Airport Operations","0","0","0"],["Background Check","0","1","0"],["Driver Reactivation","0","0","0"],["Driver Referrals","0","0","0"],["Express Drive","0","0","0"],["Fraud Review","0","0","0"],["Inspection Support","0","0","0"],["Phone Verification","0","0","0"],["Region Transfers","0","0","0"],["Rental Support","0","0","0"],["Tax Forms","0","0","0"],["Vehicle Documents","0","0","0"]]

Practice SQL

PostgreSQL

Open on desktop for the full SQL editor with syntax highlighting and autocomplete.

Up next

LSummarize Lyft Support Ticket TrendsEasy LPivot Lyft Funnel BreakdownEasy IPivot Open Issues by StoreEasy

Next question

Pivot Driver Support Ticket Status

Easy

SQL & Data Manipulation

Asked at 1 company1Case WhenPivotData Wrangling

Also asked at

Problem

Task

Requirements

Return one row per queue_name.
Count how many tickets in each queue are open, resolved, and escalated using conditional aggregation.
Exclude rows where queue_name is NULL.
Order the result by open_tickets descending, then queue_name ascending.

Table Definition

driver_support_tickets

column	type	description
ticket_id	INT	Unique ticket identifier
queue_name	VARCHAR(100)	Lyft support queue handling the ticket
ticket_status	VARCHAR(20)	Current status of the ticket
city	VARCHAR(100)	City tied to the issue
created_date	DATE	Date the ticket was created

Sample Data

ticket_id	queue_name	ticket_status	city	created_date
101	Driver Onboarding	open	Chicago	2024-06-03
102	Safety Escalations	escalated	San Francisco	2024-06-01
103	Driver Onboarding	resolved	Atlanta	2024-06-02
104	Earnings Review	open	Seattle	2024-06-04
105	Earnings Review	resolved	Chicago	2024-06-01
106	Driver Onboarding	open	Miami	2024-06-05

Expected Output

queue_name	open_tickets	resolved_tickets	escalated_tickets
Driver Onboarding	3	1	0
Earnings Review	2	1	1
Safety Escalations	1	1	2
Background Check	0	1	0

Schema

driver_support_tickets

Column	Type	Description
ticket_idPK	INT	Unique ticket identifier
queue_name	VARCHAR(100)	Lyft support queue handling the ticket
ticket_status	VARCHAR(20)	Current ticket status
city	VARCHAR(100)	City associated with the ticket
created_date	DATE	Date the ticket was created

Sample Data

Example 1

Input

{"driver_support_tickets":[["101","Driver Onboarding","open","Chicago","2024-06-03"],["102","Safety Escalations","escalated","San Francisco","2024-06-01"],["103","Driver Onboarding","resolved","Atlanta","2024-06-02"],["104","Earnings Review","open","Seattle","2024-06-04"],["105","Earnings Review","resolved","Chicago","2024-06-01"],["106","Driver Onboarding","open","Miami","2024-06-05"],["107","Safety Escalations","open","Boston","2024-06-06"],["108","Safety Escalations","resolved","Los Angeles",

Output

[["Driver Onboarding","3","1","0"],["Earnings Review","2","1","1"],["Safety Escalations","1","1","2"],["Airport Operations","0","0","0"],["Background Check","0","1","0"],["Driver Reactivation","0","0","0"],["Driver Referrals","0","0","0"],["Express Drive","0","0","0"],["Fraud Review","0","0","0"],["Inspection Support","0","0","0"],["Phone Verification","0","0","0"],["Region Transfers","0","0","0"],["Rental Support","0","0","0"],["Tax Forms","0","0","0"],["Vehicle Documents","0","0","0"]]

Problem

Task

Requirements

Return one row per queue_name.
Count how many tickets in each queue are open, resolved, and escalated using conditional aggregation.
Exclude rows where queue_name is NULL.
Order the result by open_tickets descending, then queue_name ascending.

Table Definition

driver_support_tickets

column	type	description
ticket_id	INT	Unique ticket identifier
queue_name	VARCHAR(100)	Lyft support queue handling the ticket
ticket_status	VARCHAR(20)	Current status of the ticket
city	VARCHAR(100)	City tied to the issue
created_date	DATE	Date the ticket was created

Sample Data

ticket_id	queue_name	ticket_status	city	created_date
101	Driver Onboarding	open	Chicago	2024-06-03
102	Safety Escalations	escalated	San Francisco	2024-06-01
103	Driver Onboarding	resolved	Atlanta	2024-06-02
104	Earnings Review	open	Seattle	2024-06-04
105	Earnings Review	resolved	Chicago	2024-06-01
106	Driver Onboarding	open	Miami	2024-06-05

Expected Output

queue_name	open_tickets	resolved_tickets	escalated_tickets
Driver Onboarding	3	1	0
Earnings Review	2	1	1
Safety Escalations	1	1	2
Background Check	0	1	0

Schema

driver_support_tickets

Column	Type	Description
ticket_idPK	INT	Unique ticket identifier
queue_name	VARCHAR(100)	Lyft support queue handling the ticket
ticket_status	VARCHAR(20)	Current ticket status
city	VARCHAR(100)	City associated with the ticket
created_date	DATE	Date the ticket was created

Sample Data

Example 1

Input

{"driver_support_tickets":[["101","Driver Onboarding","open","Chicago","2024-06-03"],["102","Safety Escalations","escalated","San Francisco","2024-06-01"],["103","Driver Onboarding","resolved","Atlanta","2024-06-02"],["104","Earnings Review","open","Seattle","2024-06-04"],["105","Earnings Review","resolved","Chicago","2024-06-01"],["106","Driver Onboarding","open","Miami","2024-06-05"],["107","Safety Escalations","open","Boston","2024-06-06"],["108","Safety Escalations","resolved","Los Angeles",

Output

[["Driver Onboarding","3","1","0"],["Earnings Review","2","1","1"],["Safety Escalations","1","1","2"],["Airport Operations","0","0","0"],["Background Check","0","1","0"],["Driver Reactivation","0","0","0"],["Driver Referrals","0","0","0"],["Express Drive","0","0","0"],["Fraud Review","0","0","0"],["Inspection Support","0","0","0"],["Phone Verification","0","0","0"],["Region Transfers","0","0","0"],["Rental Support","0","0","0"],["Tax Forms","0","0","0"],["Vehicle Documents","0","0","0"]]

Practice SQL

PostgreSQL

Open on desktop for the full SQL editor with syntax highlighting and autocomplete.

Up next

LSummarize Lyft Support Ticket TrendsEasy LPivot Lyft Funnel BreakdownEasy IPivot Open Issues by StoreEasy

Next question