View combining latest order per customer

View combining latest order per customer

Hard SQL Views 14 views
Explanation Complexity

Problem Statement

Create view v_customer_latest_order that shows each customer with their latest order_id and date.

Input Format

SQL DDL

Output Format

View created

Example

Customers, Orders
View created

Constraints

Use window function

Input / Output Format

Input Format
SQL DDL
Output Format
View created
Constraints
Use window function

Examples

Input:
Customers, Orders
Output:
View created

Example Solution (Public)

SQL 
CREATE VIEW v_customer_latest_order AS SELECT customer_id, order_id, order_date FROM (SELECT o.customer_id, o.order_id, o.order_date, ROW_NUMBER() OVER (PARTITION BY o.customer_id ORDER BY o.order_date DESC, o.order_id DESC) AS rn FROM Orders o) t WHERE rn = 1;

Official Solution Code

CREATE VIEW v_customer_latest_order AS SELECT customer_id, order_id, order_date FROM (SELECT o.customer_id, o.order_id, o.order_date, ROW_NUMBER() OVER (PARTITION BY o.customer_id ORDER BY o.order_date DESC, o.order_id DESC) AS rn FROM Orders o) t WHERE rn = 1;
Please login to submit solutions.
Editor
Output

                                        

                                    

                                    

                                        

                                                                                    
Please login to submit solutions.