MySQL 8.0

By using db<>fiddle, you agree to license everything you submit by Creative Commons CC0.

SELECT version();


version()


8.0.30


CREATE TABLE customer 
(
  id INTEGER PRIMARY KEY,
  customer_name TEXT,
  dept TEXT
);
CREATE TABLE invoice 
(
  invoice_number INTEGER PRIMARY KEY,
  customer_id INTEGER,

  time_issued DATE,
  time_paid DATE,

  time_canceled DATE,
  time_refunded DATE,
  total_price float
);
INSERT INTO customer VALUES
(1, 'cust1', 'Sales'),
(2, 'cust2', 'Sales'),
(3, 'cust3', 'Sales'),
(4, 'cust4', 'Sales'),
(5, 'cust5', 'Sales');

Records: 5  Duplicates: 0  Warnings: 0


INSERT INTO invoice VALUES
(1,  1, '2022-01-01', '2022-01-03',         NULL,         NULL,   1.0),
(2,  1, '2022-01-03', '2022-01-08',         NULL,         NULL,   1.0),
(3,  1, '2022-01-05', '2022-01-13',         NULL,         NULL,   1.0),

(4,  2, '2022-02-01', '2022-02-03',         NULL,         NULL,   2.0),
(5,  2, '2022-02-03', '2022-02-08',         NULL,         NULL,   2.0),
(6,  2, '2022-02-05', '2022-02-13',         NULL,         NULL,   2.0),


(7,  3, '2022-03-01',         NULL, '2022-03-07',         NULL,  3.0),
(8,  3, '2022-03-08',         NULL, '2022-03-14',         NULL,  3.4),
(9,  3, '2022-03-14',         NULL, '2022-03-21',         NULL,  3.4),

(10, 4, '2022-04-01', '2022-04-06', '2022-04-10', '2022-04-22',  4.0),  -- with 4, some refunded
(11, 4, '2022-04-02', '2022-04-07', '2022-04-12', '2022-04-25',  4.1),  -- some not!
(12, 4, '2022-04-03', '2022-04-09', '2022-04-16', '2022-04-28',  4.2),

(13, 4, '2022-04-20', '2022-04-27',         NULL,         NULL, 100.1),
(14, 4, '2022-04-21', '2022-04-29',         NULL,         NULL, 100.2);

Records: 14  Duplicates: 0  Warnings: 0


SELECT
  ROW_NUMBER() OVER (PARTITION BY c.id ORDER BY c.id, i.time_issued) AS rn,
  i.invoice_number AS invno, c.id AS cid, c.customer_name AS cname, c.dept AS cdept,
  i.time_issued AS idate, i.time_paid AS ipaid, 
  i.time_canceled AS icancel, 
  i.time_refunded AS irefund,
  LAST_VALUE(i.time_issued) OVER (PARTITION BY c.id ORDER BY c.id) AS l_inv_date,
  ROUND(COALESCE(i.total_price, 0), 2)   AS tot_price,
  ROUND(SUM(i.total_price) OVER (PARTITION By c.id ORDER BY c.id), 2) AS s_tot,
  ROUND(SUM(NULLIF(time_refunded IS NOT NULL, NULL) * total_price)
     OVER (PARTITION BY c.id ORDER BY c.id), 2) AS refund,

  ROUND(SUM(i.total_price) OVER (PARTITION By c.id ORDER BY c.id) -
  SUM(NULLIF(time_refunded IS NOT NULL, NULL) * total_price)
     OVER (PARTITION BY c.id ORDER BY c.id), 2) AS billed


FROM
  customer c
LEFT JOIN invoice i
  ON i.customer_id = c.id
ORDER BY cid, idate;


rn
invno
cid
cname
cdept
idate
ipaid
icancel
irefund
l_inv_date
tot_price
s_tot
refund
billed


1
1
1
cust1
Sales
2022-01-01
2022-01-03
null
null
2022-01-05
1
3
0
3

2
2
1
cust1
Sales
2022-01-03
2022-01-08
null
null
2022-01-05
1
3
0
3

3
3
1
cust1
Sales
2022-01-05
2022-01-13
null
null
2022-01-05
1
3
0
3

1
4
2
cust2
Sales
2022-02-01
2022-02-03
null
null
2022-02-05
2
6
0
6

2
5
2
cust2
Sales
2022-02-03
2022-02-08
null
null
2022-02-05
2
6
0
6

3
6
2
cust2
Sales
2022-02-05
2022-02-13
null
null
2022-02-05
2
6
0
6

1
7
3
cust3
Sales
2022-03-01
null
2022-03-07
null
2022-03-14
3
9.8
0
9.8

2
8
3
cust3
Sales
2022-03-08
null
2022-03-14
null
2022-03-14
3.4
9.8
0
9.8

3
9
3
cust3
Sales
2022-03-14
null
2022-03-21
null
2022-03-14
3.4
9.8
0
9.8

1
10
4
cust4
Sales
2022-04-01
2022-04-06
2022-04-10
2022-04-22
2022-04-21
4
212.6
12.3
200.3

2
11
4
cust4
Sales
2022-04-02
2022-04-07
2022-04-12
2022-04-25
2022-04-21
4.1
212.6
12.3
200.3

3
12
4
cust4
Sales
2022-04-03
2022-04-09
2022-04-16
2022-04-28
2022-04-21
4.2
212.6
12.3
200.3

4
13
4
cust4
Sales
2022-04-20
2022-04-27
null
null
2022-04-21
100.1
212.6
12.3
200.3

5
14
4
cust4
Sales
2022-04-21
2022-04-29
null
null
2022-04-21
100.2
212.6
12.3
200.3

1
null
5
cust5
Sales
null
null
null
null
null
0
null
null
null


SELECT
  cid, cname, inv_count, l_inv_date, s_tot, refund, valid_billed
FROM
(
  SELECT
    ROW_NUMBER() OVER (PARTITION BY c.id ORDER BY c.id, i.time_issued) AS rn,
    c.id AS cid, c.customer_name AS cname,
    
    COUNT(i.invoice_number) OVER (PARTITION BY c.id ORDER BY c.id) AS inv_count,
    
    LAST_VALUE(i.time_issued) OVER (PARTITION BY c.id ORDER BY c.id) AS l_inv_date,
    
    ROUND(COALESCE(SUM(i.total_price) OVER (PARTITION BY c.id ORDER BY c.id), 0), 2) AS s_tot,
    ROUND(COALESCE(SUM(NULLIF(time_refunded IS NOT NULL, NULL) * total_price)
      OVER (PARTITION BY c.id ORDER BY c.id), 0), 2) AS refund,

    ROUND(COALESCE(SUM(i.total_price) OVER (PARTITION By c.id ORDER BY c.id) -
      SUM(NULLIF(time_refunded IS NOT NULL, NULL) * total_price)
        OVER (PARTITION BY c.id ORDER BY c.id), 0), 2) AS valid_billed
FROM
  customer c
LEFT JOIN invoice i
  ON i.customer_id = c.id
) AS sub1
WHERE rn = 1
ORDER BY cid;


cid
cname
inv_count
l_inv_date
s_tot
refund
valid_billed


1
cust1
3
2022-01-05
3
0
3

2
cust2
3
2022-02-05
6
0
6

3
cust3
3
2022-03-14
9.8
0
9.8

4
cust4
5
2022-04-21
212.6
12.3
200.3

5
cust5
0
null
0
0
0

rn	invno	cid	cname	cdept	idate	ipaid	icancel	irefund	l_inv_date	tot_price	s_tot	refund	billed
1	1	1	cust1	Sales	2022-01-01	2022-01-03	null	null	2022-01-05	1	3	0	3
2	2	1	cust1	Sales	2022-01-03	2022-01-08	null	null	2022-01-05	1	3	0	3
3	3	1	cust1	Sales	2022-01-05	2022-01-13	null	null	2022-01-05	1	3	0	3
1	4	2	cust2	Sales	2022-02-01	2022-02-03	null	null	2022-02-05	2	6	0	6
2	5	2	cust2	Sales	2022-02-03	2022-02-08	null	null	2022-02-05	2	6	0	6
3	6	2	cust2	Sales	2022-02-05	2022-02-13	null	null	2022-02-05	2	6	0	6
1	7	3	cust3	Sales	2022-03-01	null	2022-03-07	null	2022-03-14	3	9.8	0	9.8
2	8	3	cust3	Sales	2022-03-08	null	2022-03-14	null	2022-03-14	3.4	9.8	0	9.8
3	9	3	cust3	Sales	2022-03-14	null	2022-03-21	null	2022-03-14	3.4	9.8	0	9.8
1	10	4	cust4	Sales	2022-04-01	2022-04-06	2022-04-10	2022-04-22	2022-04-21	4	212.6	12.3	200.3
2	11	4	cust4	Sales	2022-04-02	2022-04-07	2022-04-12	2022-04-25	2022-04-21	4.1	212.6	12.3	200.3
3	12	4	cust4	Sales	2022-04-03	2022-04-09	2022-04-16	2022-04-28	2022-04-21	4.2	212.6	12.3	200.3
4	13	4	cust4	Sales	2022-04-20	2022-04-27	null	null	2022-04-21	100.1	212.6	12.3	200.3
5	14	4	cust4	Sales	2022-04-21	2022-04-29	null	null	2022-04-21	100.2	212.6	12.3	200.3
1	null	5	cust5	Sales	null	null	null	null	null	0	null	null	null