-- Migrated from DuckDB test: test/sql/aggregate/aggregates/test_last.test
-- Test FIRST and LAST aggregate functions
-- Test with integers
CREATE TABLE five(i INTEGER, ts TIMESTAMP TIME INDEX);

Affected Rows: 0

INSERT INTO five VALUES (1, 1000), (2, 2000), (3, 3000), (4, 4000), (5, 5000);

Affected Rows: 5

SELECT last_value(i) FROM five;

+--------------------+
| last_value(five.i) |
+--------------------+
| 5                  |
+--------------------+

SELECT first_value(i) FROM five;

+---------------------+
| first_value(five.i) |
+---------------------+
| 1                   |
+---------------------+

SELECT i % 3 AS g, last_value(i) FROM five GROUP BY g ORDER BY g;

+---+--------------------+
| g | last_value(five.i) |
+---+--------------------+
| 0 | 3                  |
| 1 | 4                  |
| 2 | 5                  |
+---+--------------------+

SELECT i % 3 AS g, first_value(i) FROM five GROUP BY g ORDER BY g;

+---+---------------------+
| g | first_value(five.i) |
+---+---------------------+
| 0 | 3                   |
| 1 | 1                   |
| 2 | 2                   |
+---+---------------------+

-- Test with ORDER BY
SELECT last_value(i ORDER BY i DESC) FROM five;

+-------------------------------------------------------+
| last_value(five.i) ORDER BY [five.i DESC NULLS FIRST] |
+-------------------------------------------------------+
| 1                                                     |
+-------------------------------------------------------+

SELECT first_value(i ORDER BY i DESC) FROM five;

+--------------------------------------------------------+
| first_value(five.i) ORDER BY [five.i DESC NULLS FIRST] |
+--------------------------------------------------------+
| 5                                                      |
+--------------------------------------------------------+

SELECT i % 3 AS g, last_value(i ORDER BY i DESC) FROM five GROUP BY g ORDER BY g;

+---+-------------------------------------------------------+
| g | last_value(five.i) ORDER BY [five.i DESC NULLS FIRST] |
+---+-------------------------------------------------------+
| 0 | 3                                                     |
| 1 | 1                                                     |
| 2 | 2                                                     |
+---+-------------------------------------------------------+

SELECT i % 3 AS g, first_value(i ORDER BY i DESC) FROM five GROUP BY g ORDER BY g;

+---+--------------------------------------------------------+
| g | first_value(five.i) ORDER BY [five.i DESC NULLS FIRST] |
+---+--------------------------------------------------------+
| 0 | 3                                                      |
| 1 | 4                                                      |
| 2 | 5                                                      |
+---+--------------------------------------------------------+

-- Test with strings
CREATE TABLE strings(s VARCHAR, g INTEGER, ts TIMESTAMP TIME INDEX);

Affected Rows: 0

INSERT INTO strings VALUES ('apple', 1, 1000), ('banana', 1, 2000), ('cherry', 2, 3000), ('date', 2, 4000), ('elderberry', 3, 5000);

Affected Rows: 5

SELECT last_value(s) FROM strings;

+-----------------------+
| last_value(strings.s) |
+-----------------------+
| elderberry            |
+-----------------------+

SELECT first_value(s) FROM strings;

+------------------------+
| first_value(strings.s) |
+------------------------+
| apple                  |
+------------------------+

SELECT g, last_value(s) FROM strings GROUP BY g ORDER BY g;

+---+-----------------------+
| g | last_value(strings.s) |
+---+-----------------------+
| 1 | banana                |
| 2 | date                  |
| 3 | elderberry            |
+---+-----------------------+

SELECT g, first_value(s) FROM strings GROUP BY g ORDER BY g;

+---+------------------------+
| g | first_value(strings.s) |
+---+------------------------+
| 1 | apple                  |
| 2 | cherry                 |
| 3 | elderberry             |
+---+------------------------+

-- Test with ORDER BY on strings
SELECT last_value(s ORDER BY s) FROM strings;

+-----------------------------------------------------------+
| last_value(strings.s) ORDER BY [strings.s ASC NULLS LAST] |
+-----------------------------------------------------------+
| elderberry                                                |
+-----------------------------------------------------------+

SELECT first_value(s ORDER BY s) FROM strings;

+------------------------------------------------------------+
| first_value(strings.s) ORDER BY [strings.s ASC NULLS LAST] |
+------------------------------------------------------------+
| apple                                                      |
+------------------------------------------------------------+

SELECT g, last_value(s ORDER BY s) FROM strings GROUP BY g ORDER BY g;

+---+-----------------------------------------------------------+
| g | last_value(strings.s) ORDER BY [strings.s ASC NULLS LAST] |
+---+-----------------------------------------------------------+
| 1 | banana                                                    |
| 2 | date                                                      |
| 3 | elderberry                                                |
+---+-----------------------------------------------------------+

SELECT g, first_value(s ORDER BY s) FROM strings GROUP BY g ORDER BY g;

+---+------------------------------------------------------------+
| g | first_value(strings.s) ORDER BY [strings.s ASC NULLS LAST] |
+---+------------------------------------------------------------+
| 1 | apple                                                      |
| 2 | cherry                                                     |
| 3 | elderberry                                                 |
+---+------------------------------------------------------------+

-- Test with NULL values
INSERT INTO strings VALUES (NULL, 1, 6000), ('fig', NULL, 7000);

Affected Rows: 2

SELECT last_value(s) FROM strings;

+-----------------------+
| last_value(strings.s) |
+-----------------------+
| fig                   |
+-----------------------+

SELECT first_value(s) FROM strings;

+------------------------+
| first_value(strings.s) |
+------------------------+
| apple                  |
+------------------------+

SELECT g, last_value(s) FROM strings WHERE g IS NOT NULL GROUP BY g ORDER BY g;

+---+-----------------------+
| g | last_value(strings.s) |
+---+-----------------------+
| 1 |                       |
| 2 | date                  |
| 3 | elderberry            |
+---+-----------------------+

-- Test with dates
CREATE TABLE dates(d DATE, i INTEGER, ts TIMESTAMP TIME INDEX);

Affected Rows: 0

INSERT INTO dates VALUES ('2021-08-20', 1, 1000), ('2021-08-21', 2, 2000), ('2021-08-22', 3, 3000), ('2021-08-23', 4, 4000), ('2021-08-24', 5, 5000);

Affected Rows: 5

SELECT last_value(d) FROM dates;

+---------------------+
| last_value(dates.d) |
+---------------------+
| 2021-08-24          |
+---------------------+

SELECT first_value(d) FROM dates;

+----------------------+
| first_value(dates.d) |
+----------------------+
| 2021-08-20           |
+----------------------+

SELECT i % 3 AS g, last_value(d) FROM dates GROUP BY g ORDER BY g;

+---+---------------------+
| g | last_value(dates.d) |
+---+---------------------+
| 0 | 2021-08-22          |
| 1 | 2021-08-23          |
| 2 | 2021-08-24          |
+---+---------------------+

SELECT i % 3 AS g, first_value(d) FROM dates GROUP BY g ORDER BY g;

+---+----------------------+
| g | first_value(dates.d) |
+---+----------------------+
| 0 | 2021-08-22           |
| 1 | 2021-08-20           |
| 2 | 2021-08-21           |
+---+----------------------+

-- cleanup
DROP TABLE five;

Affected Rows: 0

DROP TABLE strings;

Affected Rows: 0

DROP TABLE dates;

Affected Rows: 0