I have a table in a database that is structures as follows:

| date      | app    | action    | response |
|-----------|--------|-----------|----------|
| 9/22/2022 | e-file | launch    | 2        |
| 9/22/2022 | e-file | login     | 3        |
| 9/22/2022 | e-file | edit      | 5        |
| 9/22/2022 | e-file | clicksave | 6        |
| 9/22/2022 | e-file | logout    | 7        |
| 9/28/2022 | cube   | launch    | 3        |
| 9/28/2022 | cube   | login     | 2        |
| 9/28/2022 | cube   | edit      | 7        |
| 9/28/2022 | cube   | clicksave | 8        |
| 9/28/2022 | cube   | logout    | 9        |

I want to achieve this result in order to use this in a grafana table:

| action    | response_e-file | response_cube | e vs cube |
|-----------|-----------------|---------------|-----------|
| launch    | 2               | 3             | 0.33      |
| login     | 3               | 2             | -0.33     |
| edit      | 5               | 7             | 0.40      |
| clicksave | 6               | 8             | 0.33      |
| logout    | 7               | 9             | 0.29      |

The response column will be split out into different columns based on the value in the app column. Additionally, there will a new column performing simple math based on the two response columns.

I'm quite new to SQL and haven't had much luck.

What would the query be to achieve this? Is this even achievable?

CodePudding user response：

We pivot using case-when and then group by action.

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

Fiddle

CodePudding user response：

You can achieve this by using nested queries and tweaking the following query to fit your use case -

SELECT action, [response_e-file], [response_cube], (response_cube - response_e-file) / response_cube as e_vs_cube FROM (
    SELECT action, col, val FROM (
        SELECT *, 'response_' app as col, response as val FROM @T
    ) t
) tt

It is unclear on how the e_vs_cube column is calculated since the values seem inconsistent.

For the first 2 rows it seems (response_cube - response_e-file) / response_cube but the others it looks like (response_cube - response_e-file) / response_e-file.