sql pratice1

some sql concepts:


select a1.machine_id, round(avg(a2.timestamp-a1.timestamp), 3) as processing_time 
from Activity a1
join Activity a2 
on a1.machine_id=a2.machine_id and a1.process_id=a2.process_id
and a1.activity_type='start' and a2.activity_type='end'
group by a1.machine_id


SELECT v.customer_id, COUNT(v.visit_id) AS count_no_trans 
from Visits v 
LEFT JOIN Transactions t 
ON v.visit_id = t.visit_id  
WHERE t.amount IS NULL 
GROUP BY v.customer_id; 


# Write your MySQL query statement below
select id from(
    select *,
            LAG(temperature) OVER (ORDER BY recordDate) AS prev_temp,
           LAG(recordDate) OVER (ORDER BY recordDate) AS prev_date
        from weather 
) b where temperature> prev_temp
and  DATEDIFF(recordDate, prev_date) = 1;


select a1.machine_id, 
ROUND(AVG(A2.timestamp-A1.timestamp) ,3)as processing_time 
from Activity A1 join Activity A2
on A1.machine_id =A2.machine_id
and A1.process_id=A2.process_id
AND a1.activity_type = 'start' 
AND a2.activity_type = 'end'
group by A1.machine_id ;




# Write your MySQL query statement below
select a.user_id,
round(avg(if(b.action="confirmed",1,0)),2) AS confirmation_rate
from Signups a  left join Confirmations b
on a.user_id=b.user_id
group by a.user_id;





# Write your MySQL query statement below
select 
s.student_id , s.student_name ,b.subject_name , count(e.subject_name) as attended_exams
from Students s
join Subjects b
left join Examinations e
on s.student_id=e.student_id and b.subject_name=e.subject_name
group by s.student_id , s.student_name ,b.subject_name 
order by s.student_id , b.subject_name;

self join concepts:

select e1.name
from Employee e1  join Employee e2
on e1.id=e2.managerId 
GROUP BY e1.id, e1.name
HAVING COUNT(e2.managerId) >= 5;

--you have to join on other columns also not only on employee_id to get reports to using self join 

select e1.employee_id ,e1.name  ,COUNT(e2.employee_id) as reports_count ,round(avg(e2.age)) as average_age
from Employees  e1  join Employees  e2
on e1.employee_id =e2.reports_to  GROUP BY E1.employee_id, E1.name 
order by employee_id


--subquery

 SELECT employee_id 
 FROM employees where salary < 30000
 and manager_id not in (select employee_id
 from Employees) order by employee_id;


SELECT s1.id, COALESCE(s2.student, s1.student) as student
FROM seat s1
LEFT JOIN seat s2
ON IF(s1.id % 2 != 0, s2.id = s1.id + 1, s2.id = s1.id - 1)


-- using not in 
identify only people who have gold medal, if if they have bronze should not show up
select gold as player_name, count(*) as counts from events
where gold not in (select bronze from events UNION select silver from events)
group by gold;




+-----+-----+
| lat | lon |
+-----+-----+
| 10  | 10  |
| 20  | 20  |
| 20  | 20  |
| 40  | 40  |
+-----+-----+


COUNT(pid) OVER (PARTITION BY tiv_2015) as same_tiv,
COUNT(pid) OVER (PARTITION BY lat, lon) as same_loc

we can compare using partition by  also in where clause same_tiv and same_loc;

SELECT ROUND(SUM(tiv_2016), 2) as tiv_2016
FROM (
    SELECT tiv_2016,
        COUNT(pid) OVER (PARTITION BY tiv_2015) as same_tiv,
        COUNT(pid) OVER (PARTITION BY lat, lon) as same_loc
    FROM Insurance
) temp
WHERE same_tiv > 1 AND same_loc = 1


		
		
LIMIT 1 OFFSET 1-----skips first row.


---using if null with in sql

SELECT 
    IFNULL(
        (SELECT DISTINCT salary 
         FROM Employee 
         ORDER BY salary DESC 
         LIMIT 1 OFFSET 1), 
        NULL) AS SecondHighestSalary;
		
		
		
		
		
		
		# Write your MySQL query statement below
with MostActiveUser as
(
    select name  from
    Users u join MovieRating r
     on u.user_id=r.user_id
    group by u.user_id
    order by count(*) desc, name
    limit 1
),

 BestMovieFeb as
(
select m.title from Movies m join MovieRating r
     on m.movie_id=r.movie_id
    where created_at between '2020-02-01' AND '2020-02-29'
    group by m.movie_id
    order by AVG(rating) desc, title
    limit 1
)

select name as results
from MostActiveUser
union all
select title 
from BestMovieFeb
;


select person_name from (SELECT person_name,turn,
SUM(weight) OVER (ORDER BY turn) AS cum
FROM Queue )a  where  cum<=1000 order by turn desc limit 1;



two tables no need to join we can try using subquery or not in etc.
we count no of product customer bout to total product in product table

SELECT customer_id
FROM customer
GROUP BY customer_id
HAVING COUNT(DISTINCT product_key) = (SELECT COUNT(DISTINCT product_key) FROM product);



select activity_date  as day, count(distinct user_id) as active_users
from Activity A1
WHERE 
    activity_date BETWEEN '2019-06-28' AND '2019-07-27'
GROUP BY 
    activity_date
ORDER BY 
    activity_date;


to get next number or comparing current row with next row 
use  t1.id = t2.id + 1 to get next consecutive number r

LAG(num,1) OVER (ORDER BY id) AS prev_num 
LAG(num,2) OVER(Order by id) AS prev_num by 2 

--WHERE num = prev1 AND num = prev2; --get consecutive numbers 


SUM(amount) OVER (ORDER BY visited_on ROWS BETWEEN 6 PRECEDING AND CURRENT ROW): 
This calculates the sum of the amount (daily totals) for the current row and the 6 rows before it,
based on the order of visited_on. This creates a 7-day moving sum.