Assume you have an events table on app
analystics. Write a query to get the app's click-through rate (CTR %) in 2022.
Output the results in percentages rounded to 2 decimal places.
Notes:
·
Percentage of click-through rate = 100.0 * Number of clicks / Number of
impressions
·
To avoid integer division, you should multiply the click-through rate by
100.0, not 100.
table name: events
Solution:
select app_id,format(100.0 * sum(case when event_type = 'click' then 1 else 0 end)/sum(case when event_type = 'impression' then 1 else 0 end),'N2') as ctr
from events
WHERE timestamp >= '2022-01-01'
AND timestamp < '2023-01-01'
group by app_id
Output:
SQL Script:
CREATE TABLE [dbo].[events](
[app_id]
[int] NULL,
[event_type]
[nvarchar](50) NULL,
[timestamp]
[datetime] NULL
) ON [PRIMARY]
GO
INSERT [dbo].[events] ([app_id], [event_type], [timestamp]) VALUES (123, N'impression', CAST(N'2022-07-18T00:00:00.000' AS DateTime))
GO
INSERT [dbo].[events] ([app_id], [event_type], [timestamp]) VALUES (123, N'impression', CAST(N'2022-07-18T00:00:00.000' AS DateTime))
GO
INSERT [dbo].[events] ([app_id], [event_type], [timestamp]) VALUES (123, N'click', CAST(N'2022-07-18T00:00:00.000' AS DateTime))
GO
INSERT [dbo].[events] ([app_id], [event_type], [timestamp]) VALUES (234, N'impression', CAST(N'2022-07-18T00:00:00.000' AS DateTime))
GO
INSERT [dbo].[events] ([app_id], [event_type], [timestamp]) VALUES (234, N'click', CAST(N'2022-07-18T00:00:00.000' AS DateTime))
GO