Interview Guides

Top Shipping Claim Reasons | Dataford Interview Questions - Dataford - Ace your Interview

Top Shipping Claim Reasons

Easy

SQL & Data Manipulation

Asked at 1 company1AggregationsGroup ByCase When

Also asked at

Problem

Task

Whatnot's Operations team wants to understand the most common reasons buyers file shipping claims in the marketplace. Write a SQL query to identify the most frequent shipping claim reasons from the shipping_claims table.

Requirements

Return each non-null claim_reason and the number of claims for that reason.
Order the results by claim count descending, then by claim_reason ascending.

Table Definition

column_name	type	description
claim_id	INT	Primary key for each shipping claim
order_id	INT	Whatnot order tied to the claim
claim_reason	VARCHAR(100)	Reason selected for the shipping claim
claim_status	VARCHAR(30)	Current status of the claim
claim_created_at	DATE	Date the claim was created

Sample Data

claim_id	order_id	claim_reason	claim_status	claim_created_at
101	5001	Item not received	open	2024-03-05
102	5002	Damaged in transit	resolved	2024-03-02
103	5003	Item not received	open	2024-03-01
104	5004	Wrong item received	denied	2024-03-06
105	5005	Damaged in transit	open	2024-03-04
106	5006	Missing items	resolved	2024-03-03
107	5007	Item not received	resolved	2024-03-07
108	5008		open	2024-03-08
109	5009	NULL	open	2024-03-09
110	5010	Damaged in transit	denied	2024-03-10

Expected Output

claim_reason	claim_count
Damaged in transit	3
Item not received	3
Missing items	2
Wrong item received	2

Schema

shipping_claims

Column	Type	Description
claim_idPK	INT	Primary key for each shipping claim
order_id	INT	Whatnot order tied to the claim
claim_reason	VARCHAR(100)	Reason selected for the shipping claim
claim_status	VARCHAR(30)	Current status of the claim
claim_created_at	DATE	Date the claim was created

shipping_claims

claim_id	order_id	claim_reason	claim_status	claim_created_at
101	5001	Item not received	open	2024-03-05
102	5002	Damaged in transit	resolved	2024-03-02
103	5003	Item not received	open	2024-03-01
104	5004	Wrong item received	denied	2024-03-06
105	5005	Damaged in transit	open	2024-03-04
106	5006	Missing items	resolved	2024-03-03
107	5007	Item not received	resolved	2024-03-07
108	5008		open	2024-03-08
109	5009		open	2024-03-09
110	5010	Damaged in transit	denied	2024-03-10
111	5011	Wrong item received	open	2024-03-11
112	5012	Missing items	resolved	2024-03-12
1	23	Package returned to sender	refunded	2024-03-13
2	13	Item not received	auto_closed	2024-03-10
3	30	null	rejected	2024-03-15
4	62	Partial shipment received	appealed	2024-02-28
5	12	Package damaged externally	in_progress	2024-03-04
6	100	Item not received	on_hold	2024-03-14
7	100	Item arrived late	open	2024-02-27
8	86	null	rejected	2024-03-02
9	41	Item not received	submitted	2024-03-03
10	87	Seller used insufficient packaging	escalated	2024-02-29
11	90	null	rejected	2024-03-04
12	22	Contents damaged	awaiting_tracking_update	2024-03-13
13	87	Hazmat restriction delay	appealed	2024-03-14
14	25	Package damaged externally	manual_review	2024-02-29
15	27	Carrier delay	open	2024-03-08

Expected Output

claim_reason	claim_count
Item not received	6
Damaged in transit	3
Missing items	2
Package damaged externally	2
Wrong item received	2
Carrier delay	1
Contents damaged	1
Hazmat restriction delay	1
Item arrived late	1
Package returned to sender	1
Partial shipment received	1
Seller used insufficient packaging	1

Problem

Task

Requirements

Return each non-null claim_reason and the number of claims for that reason.
Order the results by claim count descending, then by claim_reason ascending.

Table Definition

column_name	type	description
claim_id	INT	Primary key for each shipping claim
order_id	INT	Whatnot order tied to the claim
claim_reason	VARCHAR(100)	Reason selected for the shipping claim
claim_status	VARCHAR(30)	Current status of the claim
claim_created_at	DATE	Date the claim was created

Sample Data

claim_id	order_id	claim_reason	claim_status	claim_created_at
101	5001	Item not received	open	2024-03-05
102	5002	Damaged in transit	resolved	2024-03-02
103	5003	Item not received	open	2024-03-01
104	5004	Wrong item received	denied	2024-03-06
105	5005	Damaged in transit	open	2024-03-04
106	5006	Missing items	resolved	2024-03-03
107	5007	Item not received	resolved	2024-03-07
108	5008		open	2024-03-08
109	5009	NULL	open	2024-03-09
110	5010	Damaged in transit	denied	2024-03-10

Expected Output

claim_reason	claim_count
Damaged in transit	3
Item not received	3
Missing items	2
Wrong item received	2

Schema

shipping_claims

Column	Type	Description
claim_idPK	INT	Primary key for each shipping claim
order_id	INT	Whatnot order tied to the claim
claim_reason	VARCHAR(100)	Reason selected for the shipping claim
claim_status	VARCHAR(30)	Current status of the claim
claim_created_at	DATE	Date the claim was created

shipping_claims

claim_id	order_id	claim_reason	claim_status	claim_created_at
101	5001	Item not received	open	2024-03-05
102	5002	Damaged in transit	resolved	2024-03-02
103	5003	Item not received	open	2024-03-01
104	5004	Wrong item received	denied	2024-03-06
105	5005	Damaged in transit	open	2024-03-04
106	5006	Missing items	resolved	2024-03-03
107	5007	Item not received	resolved	2024-03-07
108	5008		open	2024-03-08
109	5009		open	2024-03-09
110	5010	Damaged in transit	denied	2024-03-10
111	5011	Wrong item received	open	2024-03-11
112	5012	Missing items	resolved	2024-03-12
1	23	Package returned to sender	refunded	2024-03-13
2	13	Item not received	auto_closed	2024-03-10
3	30	null	rejected	2024-03-15
4	62	Partial shipment received	appealed	2024-02-28
5	12	Package damaged externally	in_progress	2024-03-04
6	100	Item not received	on_hold	2024-03-14
7	100	Item arrived late	open	2024-02-27
8	86	null	rejected	2024-03-02
9	41	Item not received	submitted	2024-03-03
10	87	Seller used insufficient packaging	escalated	2024-02-29
11	90	null	rejected	2024-03-04
12	22	Contents damaged	awaiting_tracking_update	2024-03-13
13	87	Hazmat restriction delay	appealed	2024-03-14
14	25	Package damaged externally	manual_review	2024-02-29
15	27	Carrier delay	open	2024-03-08

Expected Output

claim_reason	claim_count
Item not received	6
Damaged in transit	3
Missing items	2
Package damaged externally	2
Wrong item received	2
Carrier delay	1
Contents damaged	1
Hazmat restriction delay	1
Item arrived late	1
Package returned to sender	1
Partial shipment received	1
Seller used insufficient packaging	1

Practice SQL

PostgreSQL

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

Up next

Validate Drawback Entries by ClaimEasy WTop Categories by Shipping GrowthEasy

Top Shipping Claim Reasons

Easy

SQL & Data Manipulation

Asked at 1 company1AggregationsGroup ByCase When

Also asked at

Problem

Task

Requirements

Return each non-null claim_reason and the number of claims for that reason.
Order the results by claim count descending, then by claim_reason ascending.

Table Definition

column_name	type	description
claim_id	INT	Primary key for each shipping claim
order_id	INT	Whatnot order tied to the claim
claim_reason	VARCHAR(100)	Reason selected for the shipping claim
claim_status	VARCHAR(30)	Current status of the claim
claim_created_at	DATE	Date the claim was created

Sample Data

claim_id	order_id	claim_reason	claim_status	claim_created_at
101	5001	Item not received	open	2024-03-05
102	5002	Damaged in transit	resolved	2024-03-02
103	5003	Item not received	open	2024-03-01
104	5004	Wrong item received	denied	2024-03-06
105	5005	Damaged in transit	open	2024-03-04
106	5006	Missing items	resolved	2024-03-03
107	5007	Item not received	resolved	2024-03-07
108	5008		open	2024-03-08
109	5009	NULL	open	2024-03-09
110	5010	Damaged in transit	denied	2024-03-10

Expected Output

claim_reason	claim_count
Damaged in transit	3
Item not received	3
Missing items	2
Wrong item received	2

Schema

shipping_claims

Column	Type	Description
claim_idPK	INT	Primary key for each shipping claim
order_id	INT	Whatnot order tied to the claim
claim_reason	VARCHAR(100)	Reason selected for the shipping claim
claim_status	VARCHAR(30)	Current status of the claim
claim_created_at	DATE	Date the claim was created

shipping_claims

claim_id	order_id	claim_reason	claim_status	claim_created_at
101	5001	Item not received	open	2024-03-05
102	5002	Damaged in transit	resolved	2024-03-02
103	5003	Item not received	open	2024-03-01
104	5004	Wrong item received	denied	2024-03-06
105	5005	Damaged in transit	open	2024-03-04
106	5006	Missing items	resolved	2024-03-03
107	5007	Item not received	resolved	2024-03-07
108	5008		open	2024-03-08
109	5009		open	2024-03-09
110	5010	Damaged in transit	denied	2024-03-10
111	5011	Wrong item received	open	2024-03-11
112	5012	Missing items	resolved	2024-03-12
1	23	Package returned to sender	refunded	2024-03-13
2	13	Item not received	auto_closed	2024-03-10
3	30	null	rejected	2024-03-15
4	62	Partial shipment received	appealed	2024-02-28
5	12	Package damaged externally	in_progress	2024-03-04
6	100	Item not received	on_hold	2024-03-14
7	100	Item arrived late	open	2024-02-27
8	86	null	rejected	2024-03-02
9	41	Item not received	submitted	2024-03-03
10	87	Seller used insufficient packaging	escalated	2024-02-29
11	90	null	rejected	2024-03-04
12	22	Contents damaged	awaiting_tracking_update	2024-03-13
13	87	Hazmat restriction delay	appealed	2024-03-14
14	25	Package damaged externally	manual_review	2024-02-29
15	27	Carrier delay	open	2024-03-08

Expected Output

claim_reason	claim_count
Item not received	6
Damaged in transit	3
Missing items	2
Package damaged externally	2
Wrong item received	2
Carrier delay	1
Contents damaged	1
Hazmat restriction delay	1
Item arrived late	1
Package returned to sender	1
Partial shipment received	1
Seller used insufficient packaging	1

Problem

Task

Requirements

Return each non-null claim_reason and the number of claims for that reason.
Order the results by claim count descending, then by claim_reason ascending.

Table Definition

column_name	type	description
claim_id	INT	Primary key for each shipping claim
order_id	INT	Whatnot order tied to the claim
claim_reason	VARCHAR(100)	Reason selected for the shipping claim
claim_status	VARCHAR(30)	Current status of the claim
claim_created_at	DATE	Date the claim was created

Sample Data

claim_id	order_id	claim_reason	claim_status	claim_created_at
101	5001	Item not received	open	2024-03-05
102	5002	Damaged in transit	resolved	2024-03-02
103	5003	Item not received	open	2024-03-01
104	5004	Wrong item received	denied	2024-03-06
105	5005	Damaged in transit	open	2024-03-04
106	5006	Missing items	resolved	2024-03-03
107	5007	Item not received	resolved	2024-03-07
108	5008		open	2024-03-08
109	5009	NULL	open	2024-03-09
110	5010	Damaged in transit	denied	2024-03-10

Expected Output

claim_reason	claim_count
Damaged in transit	3
Item not received	3
Missing items	2
Wrong item received	2

Schema

shipping_claims

Column	Type	Description
claim_idPK	INT	Primary key for each shipping claim
order_id	INT	Whatnot order tied to the claim
claim_reason	VARCHAR(100)	Reason selected for the shipping claim
claim_status	VARCHAR(30)	Current status of the claim
claim_created_at	DATE	Date the claim was created

shipping_claims

claim_id	order_id	claim_reason	claim_status	claim_created_at
101	5001	Item not received	open	2024-03-05
102	5002	Damaged in transit	resolved	2024-03-02
103	5003	Item not received	open	2024-03-01
104	5004	Wrong item received	denied	2024-03-06
105	5005	Damaged in transit	open	2024-03-04
106	5006	Missing items	resolved	2024-03-03
107	5007	Item not received	resolved	2024-03-07
108	5008		open	2024-03-08
109	5009		open	2024-03-09
110	5010	Damaged in transit	denied	2024-03-10
111	5011	Wrong item received	open	2024-03-11
112	5012	Missing items	resolved	2024-03-12
1	23	Package returned to sender	refunded	2024-03-13
2	13	Item not received	auto_closed	2024-03-10
3	30	null	rejected	2024-03-15
4	62	Partial shipment received	appealed	2024-02-28
5	12	Package damaged externally	in_progress	2024-03-04
6	100	Item not received	on_hold	2024-03-14
7	100	Item arrived late	open	2024-02-27
8	86	null	rejected	2024-03-02
9	41	Item not received	submitted	2024-03-03
10	87	Seller used insufficient packaging	escalated	2024-02-29
11	90	null	rejected	2024-03-04
12	22	Contents damaged	awaiting_tracking_update	2024-03-13
13	87	Hazmat restriction delay	appealed	2024-03-14
14	25	Package damaged externally	manual_review	2024-02-29
15	27	Carrier delay	open	2024-03-08

Expected Output

claim_reason	claim_count
Item not received	6
Damaged in transit	3
Missing items	2
Package damaged externally	2
Wrong item received	2
Carrier delay	1
Contents damaged	1
Hazmat restriction delay	1
Item arrived late	1
Package returned to sender	1
Partial shipment received	1
Seller used insufficient packaging	1

Practice SQL

PostgreSQL

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

Up next

Validate Drawback Entries by ClaimEasy WTop Categories by Shipping GrowthEasy