By using db<>fiddle, you agree to license everything you submit by Creative Commons CC0.
CREATE TABLE orders (
customer_id NUMBER,
order_total NUMBER,
order_date DATE
)
CREATE TABLE customers (
customer_id NUMBER,
cust_first_name VARCHAR2(20),
cust_last_name VARCHAR2(20),
income_level NUMBER
)
select
o.customer_id,
c.cust_first_name,
c.cust_last_name,
c.income_level,
to_char(o.order_date, 'DD-MON-YY HH12:MI') as order_date,
ROW_NUMBER() over (partition by o.customer_id order by o.order_date) as order#,
o.order_total,
lag(o.order_total, 1, 0) over (partition by o.customer_id order by o.customer_id)
+ lag(o.order_total, 2, 0) over (partition by o.customer_id order by o.customer_id) as last_two_orders_sum,
min(o.order_date) keep (dense_rank last order by o.customer_id) as first_order_total
from orders o, customers c
where o.customer_id = c.customer_id
ORA-00937: not a single-group group function
select
o.customer_id,
c.cust_first_name,
c.cust_last_name,
c.income_level,
to_char(o.order_date, 'DD-MON-YY HH12:MI') as order_date,
ROW_NUMBER() over (partition by o.customer_id order by o.order_date) as order#,
o.order_total,
lag(o.order_total, 1, 0) over (partition by o.customer_id order by o.customer_id)
+ lag(o.order_total, 2, 0) over (partition by o.customer_id order by o.customer_id) as last_two_orders_sum,
min(o.order_date) keep (dense_rank last order by o.customer_id) OVER () as first_order_total
from orders o
INNER JOIN customers c
ON o.customer_id = c.customer_id
select
o.customer_id,
c.cust_first_name,
c.cust_last_name,
c.income_level,
to_char(o.order_date, 'DD-MON-YY HH12:MI') as order_date,
ROW_NUMBER() over (partition by o.customer_id order by o.order_date) as order#,
o.order_total,
lag(o.order_total, 1, 0) over (partition by o.customer_id order by o.customer_id)
+ lag(o.order_total, 2, 0) over (partition by o.customer_id order by o.customer_id) as last_two_orders_sum,
MIN(o.order_date) OVER (PARTITION BY o.customer_id) as first_order_date,
MIN(o.order_total) KEEP (DENSE_RANK FIRST ORDER BY o.order_date)
OVER (PARTITION BY o.customer_id) as first_order_total
from orders o
INNER JOIN customers c
ON o.customer_id = c.customer_id