CODEWITHSUNDEEP
sqlamazon-redshift
Yahoo
Yahoo

Reputation: 4187

How to convert row to compute column value in SQL

My SQL table is in the following format, how do I convert my table to give out CTR as a column?

    Date    |Type                       |count
    --------+---------------------------+------
    1-Apr   |Clicks                     |500
    1-Apr   |Impression                 |1000
    1-Apr   |distict user Clicks        |300
    1-Apr   |distict user impressions   |450
    2-Apr   |Clicks                     |520
    2-Apr   |Impression                 |1020
    2-Apr   |distict user Clicks        |320
    3-Apr   |distict user impressions   |470

I want this to become CTR (Clickthrough rate)
eg 1 April CTR = 500/1000 % = 50% eg 2 April CTR = 520/1020 % = 42.30 %

Date    |CTR    |Distict User CTR
--------+-------+------------------
1-Apr   |50%    |66.60%
2-Apr   |42.30% |68.08





SELECT 
   date , 
   CalculateCTR,      <-- How? 
   calculate DISTINCT CTR
FROM Metrics

Upvotes: 0

Views: 178

Answers (1)

Tim Biegeleisen
Tim Biegeleisen

Reputation: 521249

A modified pivot query should work here:

SELECT
    Date,
    100.0*MAX(CASE WHEN Type = 'Clicks' THEN count END) /
        MAX(CASE WHEN Type = 'Impressions' THEN count END) AS CTR,
    100.0*MAX(CASE WHEN Type = 'distinct user Clicks' THEN count END) /
        MAX(CASE WHEN Type = 'distinct user impressions' THEN count END) AS "Distinct User CTR"
FROM Metrics
GROUP BY Date;

Upvotes: 2

Related Questions