Calculating percentages based on a few columns in a subgroup

I have a table (let’s call it Table A) like this:

ID        Device        Clicks
1         A             10
1         B             10
2         A             1
2         C             19

I would like to build a table (let’s call it Table B) from A above like this:

ID        Device        Clicks        Percentage
1         A             10            50
1         B             10            50
2         A             1             5
2         C             19            95

Again from Table B, I’d like to derive Table C where the Updated Device column for each of the ID will carry the name from the Device column only if the Percentage is >=95%. If the percentage split between Devices for each ID is anything else, we’ll simply set UpdatedDevice to Others. For example, using the data in Table B, we’ll get a Table C like below:

ID        Device        Clicks        Percentage    UpdatedDevice
1         A             10            50            Others
1         B             10            50            Others
2         A             1             5             C
2         C             19            95            C

I am wondering if there’s a way to do this with advanced SQL windowing/analytical functions in one shot instead of generating intermediate tables.

Answer

select 
    Id
  , Device
  , Clicks
  , Percentage 
  , UpdatedDevice = isnull(max(UpdatedDevice) over (partition by Id),'Others')
from (
  select *
    , Percentage = convert(int,(clicks / sum(clicks+.0) over (partition by Id))*100)
    , UpdatedDevice = case 
        when (clicks / sum(clicks+.0) over (partition by Id)) >= .95
          then Device
        end
  from t
) as cte

test setup: http://rextester.com/XKBNO39353

returns:

+----+--------+--------+------------+---------------+
| Id | Device | Clicks | Percentage | UpdatedDevice |
+----+--------+--------+------------+---------------+
|  1 | A      |     10 | 50         | Others        |
|  1 | B      |     10 | 50         | Others        |
|  2 | A      |      1 | 5          | C             |
|  2 | C      |     19 | 95         | C             |
+----+--------+--------+------------+---------------+