Movate SQL Interview Question #python #sql #coding #viral

One of the SQL questions recently asked in Movate interview.
Given us two tables, find out the result from the combination of 2 input tables.

In order to solve this questions, we used windows function - rownumber. And also used group by count in another method.

DDL
create table tablea (empid int, empname varchar(50), salary int)
create table tablea (empid int, empname varchar(50), salary int)

Insert the records
insert into tablea values(1,'AA',1000),(2,'BB',300)
insert into tableb values(2,'BB',400),(3,'CC',100)

Follow us on

[monasanthosh]

With CTE as
(select * from tablea
Union
Select * from tableb)
Select empid, ename, salary from
(Select *, row_number() over ( partition by empid order by salary asc) as RN)
From CTE)N
Where RN=1

[bhaskerreddydommata]

select distinct ab.emp_ID, ab.emp_name, min(ab.salary) over(partition by emp_id) as mivotoutput from
(select * from tablea a
union
select * from tableb b) ab

[basammani]

select * from tablea
Union
Select * from tableb where empid=3

[kingsleyarisah]

WITH CTE AS (
SELECT * FROM Table a
UNION
SELECT * FROM Table b)
SELECT empid, empname, min(salary) FROM CTE
GROUP BY empid, empname

[AshmitaGhosh-fm]

SELECT empid, ename, MIN(salary) AS salary
FROM (
SELECT * FROM Tablea
UNION ALL
SELECT * FROM Tableb
) AS combined
GROUP BY empid, ename
ORDER BY empid;

[vigneshkumara]

WITH CTE AS
(
SELECT empid, ename, salary FROM tablea UNION SELECT empid, ename, salary FROM tableb
), CTE1 AS (
SELECT empid, ename, salary, ROW_NUMBER() OVER(PARTITION BY empid, ename order by salary) as r1 from cte
)
SELECT empid, ename, salary from cte1 where r1=1;

[anirbanbiswas]

-- ---
with cte as (select * from tablea union all select * from tableb), cte2 as(
select *, row_number() over(partition by empid order by salary desc)as rn from cte) select * from cte2 where rn=1

[harshkhannashorts]

By using full join this case will be resolved.

[TheoneD]

with cte as (select * from tablea
union all
select * from tableb)



select empid, ename, min (salary) from cte
group by empid, ename