Learn Practice Projects Free datasets Resources Toolkit Pricing

Loading…

Hands-on analytics engineering training with browser-based practice, guided projects, and a human-reviewed capstone.

support@analyticsengineering.com

Learn

The course
Roadmap
Practice
Projects
Free datasets
Resources
Templates
Open-source toolkit
Jobs
Job market data
Guides
Glossary
Compare tools
Starting-point quiz

Topics

Analytics Engineering
SQL
dbt
Data Modeling
BigQuery
Snowflake
Looker
Python
Career
Interview Prep

Company

About
1-on-1 coaching
Pricing
Log in
Start free

Terms Privacy Built by Routeless

Rank the top five customers by… · Ecommerce Sales Dataset…

Home / Practice / Ecommerce Sales Dataset: SQL Practice

Rank the top five customers by net value

#5 · 5 of 5Free sample

Exercises (5)

Ecommerce Sales Dataset: SQL Practice

5 exercises

01Count orders by statusFree
02Rank product categories by item revenueFree
03Calculate net payments by yearFree
04Find the repeat-customer rate by segmentFree
05Rank the top five customers by net valueFree

Ecommerce Sales Dataset: SQL Practice

5 exercises

01Count orders by statusFree
02Rank product categories by item revenueFree
03Calculate net payments by yearFree
04Find the repeat-customer rate by segmentFree
05Rank the top five customers by net valueFree

Instructions

Rank customers by lifetime net value: successful payments on their orders minus refunds on their orders.

Return three columns: customer_id, net_value rounded to 2 decimals, and using (). Return the top 5 ordered by , then .

value_rank

RANK

customers

value_rank

customer_id

Loading the practice editor and dataset…

WorkspaceDraft autosaved

query.sql⌘/Ctrl + Enter

Loading editor…

Tables

6 tables · 39 columns

customers5 cols+

customer_idTEXT PRIMARY KEY
created_atTEXT NOT NULL
country_codeTEXT NOT NULL
acquisition_channelTEXT NOT NULL
customer_segmentTEXT NOT NULL

products6 cols+

product_idTEXT PRIMARY KEY
skuTEXT NOT NULL
product_nameTEXT NOT NULL
categoryTEXT NOT NULL
unit_costREAL NOT NULL
list_priceREAL NOT NULL

orders9 cols+

order_idTEXT PRIMARY KEY
customer_idTEXT NOT NULL
ordered_atTEXT NOT NULL
order_statusTEXT NOT NULL
currencyTEXT NOT NULL
subtotalREAL NOT NULL
tax_amountREAL NOT NULL
shipping_amountREAL NOT NULL
order_totalREAL NOT NULL

order_items7 cols+

order_item_idTEXT PRIMARY KEY
order_idTEXT NOT NULL
product_idTEXT NOT NULL
quantityINTEGER NOT NULL
unit_priceREAL NOT NULL
discount_amountREAL NOT NULL
line_totalREAL NOT NULL

payments6 cols+

payment_idTEXT PRIMARY KEY
order_idTEXT NOT NULL
paid_atTEXT NOT NULL
payment_methodTEXT NOT NULL
payment_statusTEXT NOT NULL
amountREAL NOT NULL

refunds6 cols+

refund_idTEXT PRIMARY KEY
payment_idTEXT NOT NULL
order_idTEXT NOT NULL
refunded_atTEXT NOT NULL
refund_reasonTEXT NOT NULL
amountREAL NOT NULL

The reference answer is one attempt away.

Create a free account to save your progress and reveal the worked answer for this free exercise. The complete practice library is available with Practice Pass or Mastery.

Save attempts and completed exercises
Reveal this free exercise's answer after an attempt
Return to your progress from any device
Free forever — no credit card

Create free account →Already a member? Log in →

PreviouskFind the repeat-customer rate by segment