Trying to show dates of MIN and MAX values of each customers on DB BROWSER for SQLITE

+----------------------------------+
| CUST_NO,YEAR_MONTH,TOTAL_DEPOSIT |
+----------------------------------+
| 1,201912,802452                  |
| 1,201911,30                      |
| 1,201910,370071                  |
| 2,201912,33                      |
| 2,201911,0                       |
| 2,201910,89                      |
| 3,201912,2                       |
| 3,201911,926091                  |
| 3,201910,82                      |
+----------------------------------+

On this data table I’m trying to query dates of MIN and MAX values on SQLite

Output should look like this

╔═════════════════════════════════╗
║ CUST_NO,DATE_OF_MIN,DATE_OF_MAX ║
╠═════════════════════════════════╣
║ 1,201911,201912                 ║
║ 2,201911,201910                 ║
║ 3,201912,201911                 ║
╚═════════════════════════════════╝

This is what I have done so far;

SELECT 
CUST_NO
,YEAR_MONTH
,MAX(TOTAL DEPOSIT) AS TOTAL_DEP_MAX
FROM XSELL_DATAMART 
GROUP BY CUST_NO

so I can get the date of max but somehow I need to save this date as DATE_OF_MAX and repeat this for MIN and inner select them or create two separate tables and combine them? This is where I got stuck.

Answer

The simplest way is with FIRST_VALUE() window function:

SELECT DISTINCT
  CUST_NO,
  FIRST_VALUE(YEAR_MONTH) OVER (PARTITION BY CUST_NO ORDER BY TOTAL_DEPOSIT) DATE_OF_MIN,
  FIRST_VALUE(YEAR_MONTH) OVER (PARTITION BY CUST_NO ORDER BY TOTAL_DEPOSIT DESC) DATE_OF_MAX
FROM XSELL_DATAMART

See the demo.
Results:

| CUST_NO | DATE_OF_MIN | DATE_OF_MAX |
| ------- | ----------- | ----------- |
| 1       | 201911      | 201912      |
| 2       | 201911      | 201910      |
| 3       | 201912      | 201911      |

Advertisement

Answer