Soft delete pattern

Soft delete pattern

Hard SQL DML 19 views
Explanation Complexity

Problem Statement

Instead of deleting customers, keep the row and mark it inactive. Add is_active (default 1) and set is_active=0 for customers who have no orders in the last 2 years.

Input Format

SQL DDL + DML

Output Format

Statements

Example

Customers, Orders
Rows updated

Constraints

Assume PostgreSQL-style interval

Input / Output Format

Input Format
SQL DDL + DML
Output Format
Statements
Constraints
Assume PostgreSQL-style interval

Examples

Input:
Customers, Orders
Output:
Rows updated

Example Solution (Public)

SQL 
ALTER TABLE Customers ADD COLUMN is_active INT DEFAULT 1; UPDATE Customers SET is_active = 0 WHERE customer_id NOT IN (SELECT DISTINCT customer_id FROM Orders WHERE order_date >= (CURRENT_DATE - INTERVAL '730 days'));

Official Solution Code

ALTER TABLE Customers ADD COLUMN is_active INT DEFAULT 1; UPDATE Customers SET is_active = 0 WHERE customer_id NOT IN (SELECT DISTINCT customer_id FROM Orders WHERE order_date >= (CURRENT_DATE - INTERVAL '730 days'));
Please login to submit solutions.
Editor
Output

                                        

                                    

                                    

                                        

                                                                                    
Please login to submit solutions.