Sql - query problem

Other SQL Server Topics
1

2 tables: ads and events
ads table fields: ad_id, campaign_id, status.
Events table fields: event_id, ad_id, source, event_type

event_type has values: impression, click, conversion
ad_id has values: 1, 2, 3, 4, 5, 6, 7, 8, 9, 10
CTR calculated field = number of impressions / number of clicks as a percentage
CVR calculated field = number of impressions / number of conversion as a percentage

How do i write an SQL query with the output to be:
ad_id | CTR | CVR

the output should be grouped by ad_id.

Thanks,

2

welcome

please provide your sample data in usable format as follows

declare @ads table(ad_id int, campaign_id int, status. varchar(50))

insert into @ads
selec 1, 1, 'Completed'

--something to that effect. help us help you

3

You can use a common table expression to first count the numbers by type and then make the calculations.

WITH common_table_expression (Transact-SQL) - SQL Server | Microsoft Docs

But there are several options available but as you as specific on the types I think I would start with a pivot table. When you have a query with ad_id, number of impressions, number of clicks and number of conversions then you can easily do the rest.

If you have created some sample data I'll write the query for you but you can take a look at:

Using PIVOT and UNPIVOT - SQL Server | Microsoft Docs