This example partitions the sales table with sales from the latest 4 quarters in 4 known partitions and all other historical data in another partition. Notice that all new sales data is recorded in the first partition. This form of partitioning allows one current partition with all other partitions as history sales data.
CREATE SET TABLE sales, NO FALLBACK ( store_id INTEGER, amount DECIMAL(10, 2), region CHARACTER(30), sale_date DATE FORMAT 'YYYY/MM/DD' NOT NULL) PRIMARY INDEX (store_id) PARTITION BY CASE_N(sale_date >= CURRENT_DATE /*latest data*/, sale_date < CURRENT_DATE AND sale_date >= CURRENT_DATE - INTERVAL '3' MONTH,/*previous quarter*/ sale_date < CURRENT_DATE - INTERVAL '3' MONTH AND sale_date >= CURRENT_DATE - INTERVAL '6' MONTH, sale_date < CURRENT_DATE - INTERVAL '6' MONTH AND sale_date >= CURRENT_DATE - INTERVAL '9' MONTH, NO CASE /* older than 9 months data */);