you need to check item too with my_cte as ( select *, lag(Timestamp) over (partition by Cust_ID order by timestamp) as next, lag(Searched_for) over (partition by Cust_ID order by timestamp) as itm from Sessions ) select * ,timestampdiff(minute,next,timestamp) from my_cte where (timestampdiff(minute,next,timestamp)>60 and Searched_for = itm) or next is null
DENSE_RANK would be the most appropriate here. There wouldn't be a rank=2 if there are two orders with the same order value which is highest within a segment.
with cte as( select seat_number,occupancy, sum(occupancy) over ( partition by substr(seat_number,1,1) rows between 3 preceding and current row)as rnk from cinema_tickets ) select seat_number from cte where rnk=0 and right(seat_number,1) not in(1,2,3); simple answer
with cte as( select DISTINCT(DATE(Order_timestamp)) as time_stamp1 FROM Orders where DAYOFWEEK(order_timestamp)=2 ),cte2 as( SELECT time_stamp1, COALESCE( LEAD(time_stamp1) OVER (ORDER BY time_stamp1), ADDDATE(time_stamp1, 7) ) AS time_stamp2 FROM cte ),cte3 as( SELECT cte2.time_stamp1, COUNT(*) AS total_orders, COUNT(CASE WHEN Orders.is_defective = TRUE THEN 1 ELSE NULL END) AS x1, COUNT(CASE WHEN Orders.is_completed = TRUE THEN 1 ELSE NULL END) AS x2 FROM cte2 JOIN Orders ON Orders.order_timestamp BETWEEN cte2.time_stamp1 AND cte2.time_stamp2 GROUP BY cte2.time_stamp1 ) select time_stamp1,(x1/total_orders) as deffective_rate,(x2/total_orders) as effective_rate FROM cte3; Good question
Okay to clarify it's inner join, the distinction between cross join and inner is that in cross join we get cartesian product whereas in inner join it's primarily intersection of records. Besides when two tables have same columns - we can skip ON keyword and use USING keyword. The play of keywords doesn't decide the type of join we are implementing
Somehow the example you've taken has made the concept of Joins in SQL much more complicated (I would stick with categorical examples). I believe you've showed an example of Cross Join rather than INNER Join buy not using an ON clause.
with cte as (select *, lag(occupancy,1) over(partition by substring(seat_number, 1, 1)) as prev_occ, lag(occupancy, 2) over(partition by substring(seat_number, 1, 1)) as prev_occ2, lag(occupancy, 3) over(partition by substring(seat_number, 1, 1)) as prev_occ3 from cinema_tickets) select concat(start_seat, '-', seat_number) as vacant from (select concat(substring(seat_number, 1, 1), substring(seat_number, 2, 2) - 3) as start_seat, seat_number from cte where occupancy = 0 and prev_occ = 0 and prev_occ2 = 0 and prev_occ3 = 0) a
the last error was due to order of execution. You can't use alias name in where as "select" statement run after "where" clause. . . . . . . Btw your voice is ❤.
SELECT concat(seat,' to ',concat(left(seat,1),right(seat,1)+3)) as avaibable_seats FROM ( SELECT seat, ns1, ns2, ns3, ns4 FROM ( SELECT seat, occupancy AS ns1, LEAD(occupancy, 1) OVER(PARTITION BY LEFT(seat, 1) ORDER BY seat) AS ns2, LEAD(occupancy, 2) OVER(PARTITION BY LEFT(seat, 1) ORDER BY seat) AS ns3, LEAD(occupancy, 3) OVER(PARTITION BY LEFT(seat, 1) ORDER BY seat) AS ns4 FROM cinema_tickets ) AS s WHERE ns1 = 0 AND ns2 = 0 AND ns3 = 0 AND ns4 = 0 ) AS consecutive_empty_groups;
Hey @wrestlingshorts, could you explain contradict as in? So basically the question is if I am a user 'abc' and I have raised flags on videos say v1,v2 and v3, then query has to count those flags on each video.
with cte as( SELECT *,cast(SUBSTRING(seat_number, 2, LEN(seat_number) - 1) as int) as sn, SUBSTRING(seat_number,1,1) as seat FROM cinema_tickets ),cte2 as( select *, lead(occupancy,1) over (partition by seat order by sn) as l1, lead(occupancy,2) over (partition by seat order by sn) as l2, lead(occupancy,3) over (partition by seat order by sn) as l3 from cte ) select seat_number,CONCAT(seat,sn+3) as succestive_next_seat from cte2 where occupancy+l1+l2+l3 = 0
Yes it works for all dbs sql server N other dbs. For oracle in cte just change in first cte. Like below.... With cte as( Select t. *, TO_NUMBER (SUBSTR(seat_number, 2,LENGTH(seat_number)-1)) as sn, SUBSTR(t.seat_number, 1,1) as seat From cinema_tickets t And same you can continue from cte2
If any one is looking out purely based on CTE's ;WITH CTE AS (SELECT * FROM cinema_tickets WHERE occupancy = 0 ), CTE2 AS (SELECT seat_number AS seat_number, occupancy AS occupancy, CAST(SUBSTRING(SEAT_NUMBER, 2, 2) AS INT) - (ROW_NUMBER() OVER (PARTITION BY SUBSTRING(SEAT_NUMBER, 1, 1) ORDER BY CAST(SUBSTRING(SEAT_NUMBER, 2, 2) AS INT) ) ) AS RN FROM CTE ), CTE3 AS (SELECT SUBSTRING(seat_number, 1, 1) AS SUB, RN AS RN, COUNT(RN) AS C FROM CTE2 GROUP BY SUBSTRING(seat_number, 1, 1), RN HAVING COUNT(RN) = 4 ), CTE4 AS (SELECT SUBSTRING(CTE2.SEAT_NUMBER, 1, 1) AS SEAT_ROW, CAST(SUBSTRING(CTE2.seat_number, 2, 2) AS int) AS SEAT_NUM FROM CTE3 INNER JOIN CTE2 ON SUBSTRING(CTE2.SEAT_NUMBER, 1, 1) = CTE3.SUB AND CTE2.RN = CTE3.RN WHERE CTE3.C = 4 ) SELECT SEAT_ROW, CONCAT(SEAT_ROW, CAST(MIN(SEAT_NUM) AS varchar(5))) AS MIN_SEAT, CONCAT(SEAT_ROW, CAST(MAX(SEAT_NUM) AS varchar(5))) AS MAX_SEAT FROM CTE4 GROUP BY SEAT_ROW
The reason why she didn't get answer while using tab1 is you cannot use alias names along with where or having because according to order of execution Select statement will be executed after where/having is executed. So it do not recognize the new name that is assigned in select statement (which is not executed btw) . The approach of using tab2 is brilliant. Or you can directly use occupancy+s1+s2+s3 in where statement when using tab1. Just wanted to share.
How do we write if sales region max - each sales point of particular region? Example shop 1 revenue and total of all sales maximum of particular region?
If the question is to display region, shop1, shop1 revenue, max revenue for the region Then we can create a cte to get maximum according to each region. Join this cte with the sales table on region to display the output.
with cte as (select cust_id,txn_date,cumm_tax, rank() over(order by cumm_tax) as rnk from (SELECT cust_id,txn_date,sum(txn_amt) over(partition by cust_id order by txn_date) as cumm_tax FROM customer_txn ) x where cumm_tax >=2000 ) select * from cte order by cust_id limit 1
Different approach: with tab1 as( Select *, sum(occupancy) over(partition by substring(seat_number, 1,1) order by cast(substring(seat_number,2,len(seat_number)) as int) rows between current row and 3 following) as sum_4_prec, lead(seat_number,3) over(partition by substring(seat_number, 1,1) order by cast(substring(seat_number,2,len(seat_number)) as int)) as end_seat from cinema_tickets ) Select seat_number, end_seat from tab1 where end_seat is not null and sum_4_prec = 0
with cte as ( select *, sum(txn_amt) over(partition by cust_id order by txn_date ) as rolling_sum from customer_txn ) select cust_id,min(txn_date) as dates,min(rolling_sum) as rolling_sum from cte where rolling_sum>2000 group by cust_id this should give the same result
with cte1 as (select *,sum(txn_amt) over(partition by cust_id order by cust_id,txn_date,txn_amt) as rs from customer_txn), cte2 as (select *,rank() over(partition by cust_id order by txn_date) as rn from cte1 where rs>=2000) select cte2.cust_id,cte2.txn_date from cte2 where rn=1;
