dhiraj
BAN USERFrom a user prospective, by the earliest flight, it means the earliest she could reach the destination. So, the answer should be one out of the available non-stop flights that reach the destination first.
Considering the flights table with columns and data for only 1 source and destination:
insert into flights values(1, 'NY', 'FL', '2017-01-01 01:00:00', 2);
insert into flights values(2, 'NY', 'FL', '2017-01-01 01:00:00', 3);
insert into flights values(3, 'NY', 'FL', '2017-01-01 01:30:00', 1);
select flight_id, departure_time +
interval (flight_duration_hours || ' hour') reach_time
from flights
order by reach_time
limit 1
FLIGHT_ID REACH_TIME
3 2017-01-01 02:30:00
There are 2 ways to solve the query:
--Using join on table with latest_response
WITH
latest_response AS
(
SELECT
name,
MAX(response_date) final_response_date
FROM
rsvp
GROUP BY
name
)
SELECT
COUNT(a.name)
FROM
rsvp a,
latest_response b
WHERE
a.response_date=b.final_response_date
AND a.name=b.name
AND decision='Y';
--Without join using row_number to get latest_response
WITH ordered_response AS
(select *
, ROW_NUMBER() OVER(PARTITION BY Name ORDER BY response_date desc) r
from rsvp)
SELECT count(Name)
FROM ordered_response
WHERE r = 1
and Decision = 'Y';
It is important to not to forget to include the current department in the output. Follwoing are the three solutions:
--Using rank()
with stud as
(
select student_id, department, start_date,
rank() over (partition by student_id order by Start_date) as ranking from Student
)
select a.student_id, a.department, a.start_date, b.start_date as End_Date from stud a
left join stud b on a.student_id = b.student_id
and a.Ranking = b.ranking - 1
--Using row_number()
WITH stud as
(SELECT student_id, department, start_date, row_number() over(partition by student_id order by start_date) as start_rank from student)
SELECT a.student_id, a.department, a.start_date, b.start_date as end_date FROM stud a
LEFT JOIN stud b ON (a.student_id = b.student_id AND a.start_rank = b.start_rank-1);
--Using aggregate function
SELECT student_id,
department,
start_date,
LEAD(START_DATE) OVER (partition by student_id ORDER BY department) END_DATE
FROM STUDENT
Considering there is a table that contains count info:
- dhiraj May 14, 2017Count_table (table_name, counter)
update count_table a
set counter=(select floor(count(1)%500)
from Table1)
where table_name='Table1';