Postgres 14

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

create table person(
  id int,
  time date,
  name varchar(20),
  log_type varchar(20));
  insert into person values
(  1,'2022-04-25 07:49:58.0','Brian','Rejection 1'),
(  2,'2022-04-25 07:49:58.0','Brian','Rejection 2'),
(  3,'2022-04-27 13:05:51.0','Fredd','Rejection 1'),
(  4,'2022-05-01 02:13:44.0','Janet','Rejection 1'),
(  5,'2022-05-01 03:45:06.0','Janet','Rejection 2'),
(  6,'2022-05-01 08:01:34.0','Peter','Approval'),
(  7,'2022-05-01 12:12:53.0','Frank','Rejection 2'),
(  8,'2022-05-02 01:26:38.0','Frank','Approval');
8 rows affected

SELECT
  name,
  CASE WHEN LEFT(log_type,6) = 'Reject' THEN time END R,
  CASE WHEN log_type = 'Approval' THEN time END A
FROM person;


name
r
a


Brian
2022-04-25
null

Brian
2022-04-25
null

Fredd
2022-04-27
null

Janet
2022-05-01
null

Janet
2022-05-01
null

Peter
null
2022-05-01

Frank
2022-05-01
null

Frank
null
2022-05-02


SELECT
  name,
  COUNT(CASE WHEN LEFT(log_type,6) = 'Reject' THEN time END) all_rejections,
  COUNT(CASE WHEN log_type = 'Approval' THEN time END) all_approvals,
  COUNT(DISTINCT CASE WHEN LEFT(log_type,6) = 'Reject' THEN time END) distinct_rejections,
  COUNT(DISTINCT CASE WHEN log_type = 'Approval' THEN time END) distinct_approvals
FROM person
GROUP BY name;


name
all_rejections
all_approvals
distinct_rejections
distinct_approvals


Brian
2
0
1
0

Frank
1
1
1
1

Fredd
1
0
1
0

Janet
2
0
1
0

Peter
0
1
0
1

name	r	a
Brian	2022-04-25	null
Brian	2022-04-25	null
Fredd	2022-04-27	null
Janet	2022-05-01	null
Janet	2022-05-01	null
Peter	null	2022-05-01
Frank	2022-05-01	null
Frank	null	2022-05-02