Interview Question

Question1: 
/*Create an SQL query that shows the TOP 3 authors who sold the most books in total! */ 
DECLARE @a table
(a varchar(50), b varchar(50))

insert into @a
select 
'author_1',	'book_1'
union all
select  'author_1',	'book_2'
union all
select 'author_2',	'book_3'
union all
select 'author_2',	'book_4'
union all
select 'author_2',	'book_5'
union all
select 'author_3',	'book_6'	

DECLARE @b table
(a varchar(50), b int)
insert into @b

select 
'book_1',	1000 
union all 
select 
'book_2',	1500
union all 
select 
'book_3',	34000
union all 
select 
'book_4',	29000
union all 
select 
'book_5',	40000
union all 
select 
'book_6',	4400

select  *  from @a
select *  from @b
select top 3 a.a, sum(b.b) over(partition by a.a order by b.b), ROW_NUMBER() over (partition by a.a order by b.b  desc) rnm  from @a a
join @b b
on a.b=b.a
order by ROW_NUMBER() over (partition by a.a order by b.b  desc) 


********************************

/*Write an SQL query to find out how many users 
inserted more than 1000 but less than 2000 images in their presentations! */

declare @a table   
(user_id int ,	event_date_time int)

insert into @a
select 7494212,	1535308430
union all
select 7494212,	1535308433
union all
select 1475185,	1535308444
union all
select 6946725,	1535308475
union all
select 6946725,	1535308476
union all
select 6946725,	1535308477

select user_id, count(event_date_time) from @a
group by user_id
having count(event_date_time)>= 1 and count(event_date_time)<2


****************
Print every department where the average salary per employee is lower than $500!

SELECT department_name, AVG(salaries.salary) AS avg_salaries
FROM employees
JOIN salaries
ON employees.employee_id = salaries.employee_id
GROUP BY department_name
HAVING AVG(salaries.salary) < 500;