1) Showing Employee login date, logout date and status
create table tasks (
date_value date,
state varchar(10)
);
insert into tasks values ('2019-01-01','success'),('2019-01-02','success'),
('2019-01-03','success'),('2019-01-04','fail')
,('2019-01-05','fail'),('2019-01-06','success')
2) Scripts :
create table numbers (n int);
insert into numbers values (1),(2),(3),(4),(5), (9)
3) Write a solution to swap the seat id of every 2 consecutive students. If the
number of student is odd, the id of last student is not swapped.
Scripts :
CREATE TABLE seats ( id INT, student VARCHAR(10));
INSERT INTO seats VALUES
(1, 'Amit'),(2, 'Deepa'),(3, 'Rohit'),(4, 'Anjali'),(5, 'Neha'),(6, 'Sanjay'),(7,
'Priya');
Question: Write a Query to determine the periodic sales as per
expected Output?
CREATE TABLE sales ( month_wise_sales CHAR(20), year_sales INT);
INSERT INTO sales (month_wise_sales, year_sales)
VALUES ('Jan', 15),('Feb', 22),('Mar', 35),('Apr', 45),('May', 60);
Identify employees whose salary increased by more than 10% year-
over-year. 💰
𝐓𝐚𝐛𝐥𝐞 𝐒𝐭𝐫𝐮𝐜𝐭𝐮𝐫𝐞 & 𝐃𝐚𝐭𝐚:
CREATE TABLE employees (
employee_id INT,
name VARCHAR(50),
salary DECIMAL(10, 2),
year INT,
PRIMARY KEY (employee_id, year)
);
INSERT INTO employees (employee_id, name, salary, year) VALUES
(1, 'John Doe', 50000.00, 2022),
(1, 'John Doe', 55000.00, 2023),
(2, 'Jane Smith', 60000.00, 2022),
(2, 'Jane Smith', 65000.00, 2023);
SQL Query:
𝐒𝐨𝐥𝐮𝐭𝐢𝐨𝐧:-
WITH cte AS (
SELECT *, LAG(salary) OVER (PARTITION BY employee_id ORDER BY year) AS
prev_salary
FROM employees
)
SELECT *
FROM cte
WHERE salary = 1.10 * prev_salary;
Fractal Analytics SQL Interview Question : Game of Thrones
Database
Problem Statement : Provide a solution to find house which has won
maximum no of battles, display region, house and no. of wins for
each region.
Scripts :
CREATE TABLE king (
k_no INT PRIMARY KEY,
king VARCHAR(50),
house VARCHAR(50)
);
-- Create the 'battle' table
CREATE TABLE battle (
battle_number INT PRIMARY KEY,
name VARCHAR(100),
attacker_king INT,
defender_king INT,
attacker_outcome INT,
region VARCHAR(50),
FOREIGN KEY (attacker_king) REFERENCES king(k_no),
FOREIGN KEY (defender_king) REFERENCES king(k_no)
);
delete from king;
INSERT INTO king (k_no, king, house) VALUES
(1, 'Robb Stark', 'House Stark'),
(2, 'Joffrey Baratheon', 'House Lannister'),
(3, 'Stannis Baratheon', 'House Baratheon'),
(4, 'Balon Greyjoy', 'House Greyjoy'),
(5, 'Mace Tyrell', 'House Tyrell'),
(6, 'Doran Martell', 'House Martell');
delete from battle;
-- Insert data into the 'battle' table
INSERT INTO battle (battle_number, name, attacker_king, defender_king,
attacker_outcome, region) VALUES
(1, 'Battle of Oxcross', 1, 2, 1, 'The North'),
(2, 'Battle of Blackwater', 3, 4, 0, 'The North'),
(3, 'Battle of the Fords', 1, 5, 1, 'The Reach'),
(4, 'Battle of the Green Fork', 2, 6, 0, 'The Reach'),
(5, 'Battle of the Ruby Ford', 1, 3, 1, 'The Riverlands'),
(6, 'Battle of the Golden Tooth', 2, 1, 0, 'The North'),
(7, 'Battle of Riverrun', 3, 4, 1, 'The Riverlands'),
(8, 'Battle of Riverrun', 1, 3, 0, 'The Riverlands');
--for each region find house which has won maximum no of battles. display
region, house and no of wins
select * from battle;
select * from king;
WITH wins AS (
SELECT attacker_king AS king, region FROM battle WHERE
attacker_outcome = 1
UNION ALL
SELECT defender_king AS king, region FROM battle WHERE
attacker_outcome = 0
)
SELECT
[Link],
[Link],
COUNT(*) AS no_of_wins,
RANK() OVER (PARTITION BY [Link] ORDER BY COUNT(*) DESC) AS rnk
FROM wins w
INNER JOIN king k ON [Link] = k.k_no
GROUP BY [Link], [Link]
qualify rnk=1
Problem Statement : We have a swipe table which keeps track of
employee login and logout timings.
1. Find out the time employee person spent in office on a particular
day
(Office hours = Last Logout Time - First Login Time)
2. Find out how productive he was at office on particular day.
(He might have done many swipes per day. I need to find the actual
time spend at office)
Scripts :
CREATE TABLE swipe ( employee_id INT, activity_type VARCHAR(10),
activity_time datetime);
-- Insert sample data
INSERT INTO swipe (employee_id, activity_type, activity_time) VALUES
(1, 'login', '2024-07-23 [Link]'),(1, 'logout', '2024-07-23 [Link]'),
(1, 'login', '2024-07-23 [Link]'),(1, 'logout', '2024-07-23 [Link]'),
(2, 'login', '2024-07-23 [Link]'),(2, 'logout', '2024-07-23 [Link]'),
(2, 'login', '2024-07-23 [Link]'),(2, 'logout', '2024-07-23 [Link]'),
(1, 'login', '2024-07-24 [Link]'),(1, 'logout', '2024-07-24 [Link]'),
(2, 'login', '2024-07-24 [Link]'),(2, 'logout', '2024-07-24 [Link]');
