By using db<>fiddle, you agree to license everything you submit by Creative Commons CC0.
CREATE TABLE t (
`date` date,
`app` VARCHAR(6),
`action` VARCHAR(9),
`response` INTEGER
);
INSERT INTO t
(`date`, `app`, `action`, `response`)
VALUES
('2022-9-22', 'e-file', 'launch', '2'),
('2022-9-22', 'e-file', 'login', '3'),
('2022-9-22', 'e-file', 'edit', '5'),
('2022-9-22', 'e-file', 'clicksave', '6'),
('2022-9-22', 'e-file', 'logout', '7'),
('2022-9-28', 'cube', 'launch', '3'),
('2022-9-28', 'cube', 'login', '2'),
('2022-9-28', 'cube', 'edit', '7'),
('2022-9-28', 'cube', 'clicksave', '8'),
('2022-9-28', 'cube', 'logout', '9');
Records: 10 Duplicates: 0 Warnings: 0
select * from t
| date | app | action | response |
|---|---|---|---|
| 2022-09-22 | e-file | launch | 2 |
| 2022-09-22 | e-file | login | 3 |
| 2022-09-22 | e-file | edit | 5 |
| 2022-09-22 | e-file | clicksave | 6 |
| 2022-09-22 | e-file | logout | 7 |
| 2022-09-28 | cube | launch | 3 |
| 2022-09-28 | cube | login | 2 |
| 2022-09-28 | cube | edit | 7 |
| 2022-09-28 | cube | clicksave | 8 |
| 2022-09-28 | cube | logout | 9 |
select action
,max(case app when 'e-file' then response end) as "response_e-file"
,max(case app when 'cube' then response end) as "response_cube"
,(max(case app when 'cube' then response end)-max(case app when 'e-file' then response end))/max(case app when 'e-file' then response end) as "e vs cube"
from t
group by action
| action | response_e-file | response_cube | e vs cube |
|---|---|---|---|
| launch | 2 | 3 | 0.5000 |
| login | 3 | 2 | -0.3333 |
| edit | 5 | 7 | 0.4000 |
| clicksave | 6 | 8 | 0.3333 |
| logout | 7 | 9 | 0.2857 |