select * from ProductSales

ProductID,

ProductName,

MAX(CASE WHEN Region = 'North' THEN SaleAmount END) AS North,

MAX(CASE WHEN Region = 'South' THEN SaleAmount END) AS South,

MAX(CASE WHEN Region = 'East' THEN SaleAmount END) AS East,

MAX(CASE WHEN Region = 'West' THEN SaleAmount END) AS West

ProductSales

ProductID,

ProductName;

with cte as (

select *,

station_time -min(station_time) over(partition by train_id order by station_time) as elapsed_travel_time,

lead(station_time) over(partition by train_id order by station_time) as next_time

from trainschedule

)

select train_id, station, station_time, elapsed_travel_time,

coalesce(next_time-station_time, '00:00:00') as time_to_next_station

from cte

WITH status_changes AS (

SELECT

service_name,

updated_time,

status,

LAG(status) OVER (PARTITION BY service_name ORDER BY updated_time) AS prev_status

FROM

service_status

)

, down_periods AS (

SELECT

service_name,

updated_time AS start_updated_time,

LEAD(updated_time) OVER (PARTITION BY service_name ORDER BY updated_time) AS end_updated_time,

status, prev_status

FROM

status_changes

WHERE

(status = 'down' AND (prev_status IS NULL OR prev_status != 'down')) OR

(status != 'down' AND prev_status = 'down')

)

service_name,

start_updated_time,

end_updated_time - INTERVAL '1 second' AS end_updated_time,

status

down_periods

status = 'down' AND end_updated_time - start_updated_time >INTERVAL '3 minutes';

select * from activity

SELECT machine_id,

SUM(CASE WHEN activity_type = 'Start' THEN -time_taken ELSE time_taken END) / 2 AS processing_time

from activity

group by machine_id

with cte as (

select machine_id, process_id,

activity_type,

case when activity_type = 'Start' then -time_taken else time_taken end as total_Time

from activity

)

select machine_id,

sum(total_time) / 2 as processing_time

from cte

group by machine_id

| service_name | updated_time | status |

| hdfs | 2024-03-06 10:00:00 | up |

| hdfs | 2024-03-06 10:01:00 | up |

| hdfs | 2024-03-06 10:02:00 | down |

| hdfs | 2024-03-06 10:03:00 | down |

| hdfs | 2024-03-06 10:04:00 | down |

| hdfs | 2024-03-06 10:05:00 | down |

| hdfs | 2024-03-06 10:06:00 | down |

| hdfs | 2024-03-06 10:07:00 | up |

| hdfs | 2024-03-06 10:08:00 | up |

| hdfs | 2024-03-06 10:09:00 | down |

| hdfs | 2024-03-06 10:10:00 | down |

Problem: Find the start time and end time if the service stays down for more than 3 minutes.

| service_name | start_updated_time | end_updated_time | status |

| hdfs | 2024-03-06 10:02:00 | 2024-03-06 10:06:00 | down |

with cte as (

select *, lag(status) over(order by updated_time) as prev_status from service_status2

),

cte2 as (select *,

sum

(

case when status='down' and prev_status='up' then 1

when status='up' and prev_status='down' then 1 else 0 end

) over(order by updated_time)

as group_key from cte),

cte3 as

(

select service_name,min(status) as service_status ,min(updated_time) as start_updated_time,max(updated_time) as end_updated_time

from cte2 group by group_key,service_name )

select service_name,service_status,start_updated_time,end_updated_time from cte3 where DATEDIFF(mi,start_updated_time,end_updated_time)>=3;

CREATE TABLE Employees (

EmployeeID SERIAL PRIMARY KEY,

ManagerID INT REFERENCES Employees(EmployeeID) ON DELETE SET NULL,

EmployeeName VARCHAR(255) NOT NULL

);

INSERT INTO Employees (EmployeeID, ManagerID, EmployeeName) VALUES

(1, NULL, 'Alice'),

(2, 1, 'Bob'),

(3, 1, 'Charlie'),

(4, 2, 'David'),

(5, 2, 'Eve'),

(6, 3, 'Frank'),

(7, 3, 'Grace');

SELECT * FROM employees

WITH RECURSIVE EmployeeHierarchy AS (

-- Anchor member: Start with the top-level managers (those without a manager)

SELECT

EmployeeID,

ManagerID,

EmployeeName,

CAST(EmployeeName AS VARCHAR(255)) AS HierarchyPath,

1 AS Level

FROM Employees

WHERE ManagerID IS NULL

UNION ALL

-- Recursive member: Join the CTE with the Employees table to get the subordinates

SELECT

e.EmployeeID,

e.ManagerID,

e.EmployeeName,

CAST(eh.HierarchyPath || ' -> ' || e.EmployeeName AS VARCHAR(255)) AS HierarchyPath,

eh.Level + 1 AS Level

FROM Employees e

INNER JOIN EmployeeHierarchy eh

ON e.ManagerID = eh.EmployeeID

)

EmployeeID,

ManagerID,

EmployeeName,

HierarchyPath,

Level

FROM EmployeeHierarchy

ORDER BY HierarchyPath;