itsmeashishsingh
BAN USERselect id from Genre where Genre ='Action' and id in
(select id from Genre where Genre='Comedy');
select t2.zip_code,avg(t1.price) from table2 t2
left outer join table1 t1 on
t2.transaction_id=t1.transaction_id
group by t2.zip_code
having avg(t1.price) > 5
select distinct B.customer_id from (
select A.customer_id,rank(A.total_expenses) over(order by A.total_expenses desc) as Ranks from (
select customer_id,sum(expenses) as total_expenses from customer
group by customer_id ) A ) B
where B.Ranks <=5
Is it total number of orders or total amount of orders. Table structure is not complete either..
select o.cust_id,c.cust_name,sum(o.amount) as total_amt from orders o
left outer join customers c on
o.cust_id=c.cust_id
group by o.cust_id,c.cust_name
having count(o.order_id) >0
OR
select o.cust_id,c.cust_name,count(o.order_id) as total_orders from orders o
left outer join customers c on
o.cust_id=c.cust_id
group by o.cust_id,c.cust_name
having count(o.order_id) >0
select
distinct
ob.id as object_id
,ob.name as object_name
,A.color
,A.height
,A.length
,A.width
from ob
left outer join
(select
objatt.obid
,string_agg(case when att.name='color' then attvalue else null end,'') as color
,string_agg(case when att.name='hight' then attvalue else null end,'') as height
,string_agg(case when att.name='lenght' then attvalue else null end,'') as length
,string_agg(case when att.name='width' then attvalue else null end,'') as width
from objatt
left outer join att on
objatt.attid=att.id
group by objatt.obid) A on
ob.id=A.obid
select f.id as flight_id from flights f
where f.start_time > current_time and f.source='A' and f.dest='B'
order by (f.start_time - current_time) desc limit 1;
select count(*) from rsvp r1
inner join (select name,max(date) as max_date from RSVP group by name) r2
on r1.name=r2.name
and r1.date=r2.max_date
where r1.decision='Y'
Can someone check and confirm if this is correct -
select inv_id,supp_id,inv_date,inv_amt from (
select i.id as inv_id,s.id as supp_id,i.inv_date,i.inv_amt,dense_rank() over(partition by i.id,s.id,i.inv_date order by i.inv_amt desc) as ranks from invoice i
left outer join supplier s
on i.supp_id=s.id
where date_part('year',i.inv_date)='2016' ) A
where A.ranks=1
This is assuming that there is no department for unstaffed..
select coalease(d.name,'Unstaffed'),count(e.id) as No_of_Employees from Employee e
inner join Department d
on e.dept_id=d.dept_id
group by d.name
order by No_of_Employees desc,d.name asc;
I dont think we need to order by or partition by any columns because the question says that they need start_date of the next record as end_date of first record irrespective of its value.
This is what I came up with -
with lead function -
select student_id,department,start_date,lead(start_date,1) over() as "End_date" from student;
without lead function -
SELECT a.student_id, a.department, a.start_date,
(SELECT b.start_date FROM student b WHERE a.start_date < b.start_date
ORDER BY b.start_date limit 1) AS End_Date
FROM student a
Please let me know if this seems correct.
I dont think there will be a single invoice so anything with limit 1 or TOP wont be the right solution, We cant group by either because then we need to use aggregate functions ( max, min) which we are not allowed to use.
This is what I came up with, Let me know if this is wrong -
select inv_id,supp_id,supp_name,inv_amt from (
select i.inv_id,i.supp_id,s.supp_name,i.inv_amt, rank() over(partition by i.inv_id order by i.inv_amt desc) as "Rank" from invoice i
left outer join Supplier s
on i.supp_id=s.supp_id
where year(i.payment_date)='2016' ) A
where A.Rank = 1
Please note that I am joining supplier table to get supp_name, if we dont need supplier name then that join can be removed.
Select d.name as "Department Name",Count(e.id) as "No of Employees" from Department d
left outer join Employees e
on d.dept_id=e.dept_id
group by d.name
order by count(e.id) desc,d.name asc;
select max(salary) from employee where salary < select max(salary) from employee;
- itsmeashishsingh November 17, 2018
select c.condidateId,c.name as condidate_name,count(v.Id) as no_of_votes from vote v
- itsmeashishsingh February 08, 2019inner join candidate c
on v.candidateId=c.id
group by c.condidateId,c.name
having no_of_votes=(select max(no_of_votes) from (
select condidateId,count(Id) as no_of_votes from vote group by condidateId) A)
OR
select c.condidateId,c.name as condidate_name,count(v.Id) as no_of_votes from vote v
inner join candidate c
on v.candidateId=c.id
group by c.condidateId,c.name
having no_of_votes=(select condidateId,count(Id) as no_of_votes from vote group by condidateId order by no_of_votes desc limit 1)