Amazon Interview Question for Analysts

• 0

Comment hidden because of low score. Click to expand.
1
of 3 vote

The following a working SQL query. You can try it yourself.
-----------------------------------------------------------------------------------

``````SELECT     TEMP.EmpID, TEMP.EmpName,
Employees_HoursRate.hour_rate *  TEMP.TotalHoursPerMonth AS SAL
FROM         Employees_HoursRate INNER JOIN
(SELECT     Employee.empid AS EmpID, Employee.empname AS EmpName,
SUM(Employee_TimesSheet.no_of_hours) AS TotalHoursPerMonth
FROM        Employee INNER JOIN
Employee_TimesSheet ON Employee.empid = Employee_TimesSheet.empid
GROUP BY Employee.empid, Employee.empname,
DATEPART(month, Employee_TimesSheet.date)) AS TEMP ON
Employees_HoursRate.empid = TEMP.EmpID

ORDER BY TEMP.EmpID``````

Comment hidden because of low score. Click to expand.
0

You might want to include "year" in your group by , otherwise you are adding monthly salary of all years.

Comment hidden because of low score. Click to expand.
1
of 1 vote

create table employee(eid smallint,ename varchar(10));

insert into employee values(1 ,'Abhishek');
insert into employee values(2, 'Nikhil');
insert into employee values(3,'Rama');
insert into employee values(4 , 'Vidyut');
insert into employee values(5 , 'Pavan');

select * from timesheet;

create table timesheet(eid smallint,no_hrs smallint,cdate date);

insert into timesheet values(1,100 , '2011-06-30');
insert into timesheet values(2 , 105 , '2011-06-30');
insert into timesheet values(3 , 90 , '2011-06-30');
insert into timesheet values(4 , 85 , '2011-06-30');
insert into timesheet values(5 , 110 , '2011-06-30');

insert into timesheet values(5 , 110 , '2011-04-30');
insert into timesheet values(5 , 110 , '2011-06-30');
insert into timesheet values(4 , 110 , '2010-06-30');
insert into timesheet values(3 , 110 , '2011-03-30');
insert into timesheet values(2 , 110 , '2011-06-30');
insert into timesheet values(4 , 110 , '2011-08-30');
insert into timesheet values(5 , 110 , '2011-06-30');
insert into timesheet values(2 , 110 , '2010-06-30');
insert into timesheet values(1 , 110 , '2011-06-30');
insert into timesheet values(1 , 110 , '2010-06-30');
insert into timesheet values(3 , 110 , '2011-06-30');
insert into timesheet values(4 , 110 , '2010-06-30');

select * from hr_rate;
+------+---------+
| eid | hr_rate |
+------+---------+
| 1 | 50 |
| 2 | 60 |
| 3 | 40 |
| 4 | 30 |
| 5 | 80 |

create table hr_rate(eid smallint,hr_rate smallint);

insert into hr_rate values(1,50);
insert into hr_rate values(2,60);
insert into hr_rate values(3,40);
insert into hr_rate values(4,30);
insert into hr_rate values(5,80);

select e.ename,month(t.cdate),year(t.cdate),sum(t.no_hrs*hr.hr_rate)
from employee e,timesheet t,hr_rate hr
where e.eid=t.eid and e.eid=hr.eid
group by e.ename,month(t.cdate),year(t.cdate)
order by ename,year(t.cdate),month(t.cdate)

Comment hidden because of low score. Click to expand.
0
of 0 vote

``````SELECT
E1.empid,
E1.empname,
E2.Hours_rate*E3.total as SAL
FROM
PWM.EMP as E1,
PWM.emp_rate as E2,
(
SELECT
EMPID,
SUM(HOURS) as total
FROM
PWM.EMP_TIMESHEET
WHERE
DATE     > '2011-01-01'
and DATE < '2011-10-01'
GROUP BY
EMPID
) as E3 where E1.empid = E2.empid and E1.empid =E3.empid``````

Comment hidden because of low score. Click to expand.
0

this looks to be wrong.. so is many other queries that are here...the hours and the hour rate of each employee can differ.. if so you can multiplication like this...dont write any SQL.. just try this in a spreadsheet.. you will get to know sum(hours)* hour_rate can lead you to wrong results if the hour rate is different....

SELECT SUM(HOUR_RATE*number_of_hours) FROM EMPLOYEE where date between date_1 and date_2 would be the correct SQL

Comment hidden because of low score. Click to expand.
0
of 0 vote

select emp.empname, ab.yearmonth, total_hours*ehr.hour_rate
from employee emp
inner join
(
select b.yearmonth,b.empid, sum(b.no_of_hours) total_hours
(
select CAST(datepart(year,ts.date) AS VARCHAR(6)) + CAST(datepart(month,ts.date) AS VARCHAR(6)) as yearmonth,
ts.empid, ts.no_of_hours, date
from Employee_timesheet ts
) b
group by b.yearmonth, b.empid
) ab on ab.empid = emp.empid
inner join employee_hour_rate ehr on ehr.empid = emp.empid

Comment hidden because of low score. Click to expand.
0
of 0 vote

I believe following query shud work. Natural join. Group by month

select EE.empname, ET.yearmonth, sum(ET.total_hours)*EHR.hour_rate payrate
from Employee EE, Employee_timesheet ET, Employee_hour_rate EHR
group by extract(month from date)

Comment hidden because of low score. Click to expand.
0
of 0 vote

select e.empid,
e.empname,
to_date(date,'MM') as month,
sum(et.no_of_hours)*ehr.hour_rate
from Employee as e, Employee_timesheet as et, Employee_hour_rate as ehr
where e.empid = et.empid
and e.empid = ehr.empid
group by e.empid, e.empname, to_date(date,'MM'), ehr.hour_rate

Comment hidden because of low score. Click to expand.
0
of 0 vote

SELECT
e.empid,
e.empname,
CAST(YEAR(t.date) AS VARCHAR) + '/' + CAST(MONTH(t.date) AS VARCHAR) AS ReportingMonth,
SUM(no_of_hours) * r.hour_rate AS Total
FROM
Employee AS e INNER JOIN
Employee_timesheet AS t ON e.empid = t.empid INNER JOIN
Employee_hour_rate AS r ON e.empid = r.empid
GROUP BY
e.empid, e.empname, r.hour_rate, CAST(YEAR(t.date) AS VARCHAR) + '/' + CAST(MONTH(t.date) AS VARCHAR)

Comment hidden because of low score. Click to expand.
0
of 0 vote

select e.epname,v.total from Employee e JOIN
(select sum(empt.hours)*empr.rate TOTAL_SAL,empid
FROM employee_timesheet empt JOIN employee_hour_rate empr
ON empid
WHERE
empt.date between (select trunc(sysdate, 'MM')as start_date from dual) AND (select add_months(trunc(sysdate, 'MM'), 1) -1 as last_date from dual)
group by empid)
USING empid

Comment hidden because of low score. Click to expand.
0

select e.epname,v.total from Employee e JOIN
(select sum(empt.hours)*empr.rate TOTAL_SAL,empid
FROM employee_timesheet empt JOIN employee_hour_rate empr
ON empid
WHERE
empt.date between (select trunc(sysdate, 'MM')as start_date from dual) AND (select add_months(trunc(sysdate, 'MM'), 1) -1 as last_date from dual)
group by empid)v
USING empid

Comment hidden because of low score. Click to expand.
0
of 0 vote

SELECT E.ENAME,SUM(ET.NO_OF_HOURS*ER.HOUR_DATE) FROM EMPLOYEE E,EMPLOYEE_TIMESHEET ET,EMPLOYEE_HOR_RATE EH
WHERE E.EMPID=ET.EMPID AND EH.EMPID=E.EMPID
AND TO_CHAR(DATE,'MM')=TO_CHAR(SYSDATE,'MM')
GROUP BY E.ENAME

Comment hidden because of low score. Click to expand.
0
of 0 vote

``````create table employee (
empid int auto_increment primary key,
name varchar(20))TYPE=InnoDB;

create table employee_time(
timeid int(10) auto_increment primary key,
emp_id int,
no_of_hours int(10),
edate datetime,
index(emp_id),
foreign key (emp_id) references employee(empid))TYPE=InnoDB;

create table employee_rate(
rateid int auto_increment primary key,
emp_id int,
rate int,
foreign key (emp_id) references employee(empid))TYPE=InnoDB;

insert into employee(empid, name) values(1, 'adam');
insert into employee(empid, name) values(2, 'eve');

insert into employee_time(emp_id,no_of_hours,edate) values(1,8,'2013-02-26');
insert into employee_time(emp_id,no_of_hours,edate) values(1,8,'2013-02,25');
insert into employee_time(emp_id,no_of_hours,edate) values(1,8,'2013-02-24');
insert into employee_time(emp_id,no_of_hours,edate) values(1,8,'2013-03-01');

insert into employee_time(emp_id,no_of_hours,edate) values(2,8,'2013-02-26');
insert into employee_time(emp_id,no_of_hours,edate) values(2,8,'2013-02,25');

insert into employee_rate(emp_id,rate) values(1,10);
insert into employee_rate(emp_id,rate) values(2,10);

select * from employee;
select * from employee_time;
select * from employee_rate;

select name, sum(no_of_hours*rate) as salary from employee e,employee_time t, employee_rate r
where e.empid = t.emp_id and t.emp_id = r.emp_id and t.edate between '2013-02-01' and '2013-02-28' group by name``````

Comment hidden because of low score. Click to expand.
0
of 0 vote

select e.empid, e.empname, T.yearR, T.monthR, T.salary
from guol.emp e left join
(
select h.empid as eid, extract(year from t.dateE) as yearR, extract(month from t.dateE) as monthR, sum(t.no_of_hours*h.rate) as salary
from guol.emp_h h, guol.emp_t t
where h.empid=t.empid
group by h.empid, extract(year from t.dateE), extract(month from t.dateE) ) T on e.empid=T.eid
;

Comment hidden because of low score. Click to expand.
0
of 0 vote

CREATE TABLE #EMPLOYEE(EMPID INT,EMPNAME VARCHAR(30))
CREATE TABLE #EMPLOYEE_TIMESHEET(EMPID INT,NOOFHOURS NUMERIC(9,3),DATE SMALLDATETIME)
CREATE TABLE #EMPLOYYEHOURRATE (EMPID INT,HOUR_RATE NUMERIC(9,3))

INSERT INTO #EMPLOYEE (EMPID,EMPNAME)
VALUES (1,'SAI')
,(2,'MIKE')
,(3,'JOHN')
SELECT'EMPLOYEE' SELECT * FROM #EMPLOYEE
INSERT INTO #EMPLOYEE_TIMESHEET (EMPID,NOOFHOURS,DATE)
VALUES (1,40,GETDATE())
,(1,45,'4/30/2013')
,(1,45,'4/29/2013')
,(2,40,GETDATE())
,(2,50,'12/1/2012')
,(2,75,'12/31/2012')
,(3,40,GETDATE())
SELECT'EMPLOYEE_TIMESHEET' SELECT * FROM #EMPLOYEE_TIMESHEET
INSERT INTO #EMPLOYYEHOURRATE (EMPID,HOUR_RATE)
VALUES (1,'45')
,(2,'50')
,(3,60)
SELECT'EMPLOYEE_HOURRATE' SELECT * FROM #EMPLOYYEHOURRATE
SELECT E.EMPNAME,SUM(ET.NOOFHOURS * EHR.HOUR_RATE) AS PAY,MONTH(ET.DATE) AS Month,YEAR(ET.DATE) AS Year
FROM #EMPLOYEE E
INNER JOIN #EMPLOYEE_TIMESHEET ET ON ET.EMPID = E.EMPID
INNER JOIN #EMPLOYYEHOURRATE EHR ON EHR.EMPID = E.EMPID
GROUP BY E.EMPNAME,MONTH(ET.DATE),YEAR(ET.DATE)
ORDER BY 1
DROP TABLE #EMPLOYYEHOURRATE
DROP TABLE #EMPLOYEE_TIMESHEET
DROP TABLE #EMPLOYEE

Comment hidden because of low score. Click to expand.
0
of 0 vote

CREATE TABLE #EMPLOYEE(EMPID INT,EMPNAME VARCHAR(30))
CREATE TABLE #EMPLOYEE_TIMESHEET(EMPID INT,NOOFHOURS NUMERIC(9,3),DATE SMALLDATETIME)
CREATE TABLE #EMPLOYYEHOURRATE (EMPID INT,HOUR_RATE NUMERIC(9,3))

INSERT INTO #EMPLOYEE (EMPID,EMPNAME)
VALUES (1,'SAI')
,(2,'MIKE')
,(3,'JOHN')
SELECT'EMPLOYEE' SELECT * FROM #EMPLOYEE
INSERT INTO #EMPLOYEE_TIMESHEET (EMPID,NOOFHOURS,DATE)
VALUES (1,40,GETDATE())
,(1,45,'4/30/2013')
,(1,45,'4/29/2013')
,(2,40,GETDATE())
,(2,50,'12/1/2012')
,(2,75,'12/31/2012')
,(3,40,GETDATE())
SELECT'EMPLOYEE_TIMESHEET' SELECT * FROM #EMPLOYEE_TIMESHEET
INSERT INTO #EMPLOYYEHOURRATE (EMPID,HOUR_RATE)
VALUES (1,'45')
,(2,'50')
,(3,60)
SELECT'EMPLOYEE_HOURRATE' SELECT * FROM #EMPLOYYEHOURRATE
SELECT E.EMPNAME,SUM(ET.NOOFHOURS * EHR.HOUR_RATE) AS PAY,MONTH(ET.DATE) AS Month,YEAR(ET.DATE) AS Year
FROM #EMPLOYEE E
INNER JOIN #EMPLOYEE_TIMESHEET ET ON ET.EMPID = E.EMPID
INNER JOIN #EMPLOYYEHOURRATE EHR ON EHR.EMPID = E.EMPID
GROUP BY E.EMPNAME,MONTH(ET.DATE),YEAR(ET.DATE)
ORDER BY 1
DROP TABLE #EMPLOYYEHOURRATE
DROP TABLE #EMPLOYEE_TIMESHEET
DROP TABLE #EMPLOYEE

Comment hidden because of low score. Click to expand.
0
of 0 vote

``````select
emp.eid,
to_char(emp_timesheet.edate, 'MM-YYYY') month_year,
sum(emp_timesheet.no_hrs) total_hrs_month,
(sum(emp_timesheet.no_hrs) * emp_hour_rate.hour_rate) pay_check
from
emp, emp_timesheet, emp_hour_rate
where
emp.eid = emp_timesheet.eid and emp.eid = emp_hour_rate.eid
group by
emp.eid, to_char(emp_timesheet.edate, 'MM-YYYY'), emp_hour_rate.hour_rate``````

Comment hidden because of low score. Click to expand.
0
of 0 vote

create database amazon
go
use amazon
go

create table employee(eid smallint,ename varchar(10));
insert into employee values(1 ,'Abhishek');
insert into employee values(2, 'Nikhil');
insert into employee values(3,'Rama');
insert into employee values(4 , 'Vidyut');
insert into employee values(5 , 'Pavan');

select * from timesheet;

create table timesheet(eid smallint,no_hrs smallint,cdate date);

insert into timesheet values(1,100 , '2011-06-30');
insert into timesheet values(2 , 105 , '2011-06-30');
insert into timesheet values(3 , 90 , '2011-06-30');
insert into timesheet values(4 , 85 , '2011-06-30');
insert into timesheet values(5 , 110 , '2011-06-30');

insert into timesheet values(5 , 110 , '2011-04-30');
insert into timesheet values(5 , 110 , '2011-06-30');
insert into timesheet values(4 , 110 , '2010-06-30');
insert into timesheet values(3 , 110 , '2011-03-30');
insert into timesheet values(2 , 110 , '2011-06-30');
insert into timesheet values(4 , 110 , '2011-08-30');
insert into timesheet values(5 , 110 , '2011-06-30');
insert into timesheet values(2 , 110 , '2010-06-30');
insert into timesheet values(1 , 110 , '2011-06-30');
insert into timesheet values(1 , 110 , '2010-06-30');
insert into timesheet values(3 , 110 , '2011-06-30');
insert into timesheet values(4 , 110 , '2010-06-30');

create table hr_rate(eid smallint,hr_rate smallint);

insert into hr_rate values(1,50);
insert into hr_rate values(2,60);
insert into hr_rate values(3,40);
insert into hr_rate values(4,30);
insert into hr_rate values(5,80);

select hr.eid,t.totalworkhrs,hr.hr_rate, t.totalworkhrs*hr.hr_rate totalsal from hr_rate hr
inner join (
select eid, sum(no_hrs) as totalworkhrs
from timesheet
where year(cdate)=2011 and month(cdate)=6
group by eid) t
on t.eid = hr.eid

Comment hidden because of low score. Click to expand.
0
of 0 vote

create database amazon
go
use amazon
go

create table employee(eid smallint,ename varchar(10));
insert into employee values(1 ,'Abhishek');
insert into employee values(2, 'Nikhil');
insert into employee values(3,'Rama');
insert into employee values(4 , 'Vidyut');
insert into employee values(5 , 'Pavan');

select * from timesheet;

create table timesheet(eid smallint,no_hrs smallint,cdate date);

insert into timesheet values(1,100 , '2011-06-30');
insert into timesheet values(2 , 105 , '2011-06-30');
insert into timesheet values(3 , 90 , '2011-06-30');
insert into timesheet values(4 , 85 , '2011-06-30');
insert into timesheet values(5 , 110 , '2011-06-30');

insert into timesheet values(5 , 110 , '2011-04-30');
insert into timesheet values(5 , 110 , '2011-06-30');
insert into timesheet values(4 , 110 , '2010-06-30');
insert into timesheet values(3 , 110 , '2011-03-30');
insert into timesheet values(2 , 110 , '2011-06-30');
insert into timesheet values(4 , 110 , '2011-08-30');
insert into timesheet values(5 , 110 , '2011-06-30');
insert into timesheet values(2 , 110 , '2010-06-30');
insert into timesheet values(1 , 110 , '2011-06-30');
insert into timesheet values(1 , 110 , '2010-06-30');
insert into timesheet values(3 , 110 , '2011-06-30');
insert into timesheet values(4 , 110 , '2010-06-30');

create table hr_rate(eid smallint,hr_rate smallint);

insert into hr_rate values(1,50);
insert into hr_rate values(2,60);
insert into hr_rate values(3,40);
insert into hr_rate values(4,30);
insert into hr_rate values(5,80);

select hr.eid,t.totalworkhrs,hr.hr_rate, t.totalworkhrs*hr.hr_rate totalsal from hr_rate hr
inner join (
select eid, sum(no_hrs) as totalworkhrs
from timesheet
where year(cdate)=2011 and month(cdate)=6
group by eid) t
on t.eid = hr.eid

Comment hidden because of low score. Click to expand.
0
of 0 vote

How about something primitive like this? if there are any performance issues/better way to write this query please correct me.

SELECT
EMP.EMPID, EMPNAME, TS_YEAR, TS_MONTH, (TS_TOTAL_HOURS*HOURLY_RATE) AS SALARY
FROM
EMPLOYEE EMP
LEFT JOIN
(SELECT EMP_ID,EXTRACT(YEAR FROM TS_DATE) AS TS_YEAR,
EXTRACT(MONTH FROM TS_DATE) AS TS_MONTH,
SUM(NO_OF_HOURS) AS TS_TOTAL_HOURS
FROM EMPLOYEE_TIMESHEET
GROUP BY EMP_ID,EXTRACT(YEAR FROM TS_DATE),EXTRACT(MONTH FROM TS_DATE)) TS
ON TS.EMP_ID = EMP.EMPID
JOIN EMPLOYEE_HOUR_RATE EHR
ON EMP.EMP_ID = EHR.EMPID

Comment hidden because of low score. Click to expand.
-1
of 1 vote

mysql> select * from employee;
+-----+----------+
| eid | ename |
+-----+----------+
| 1 | Abhishek |
| 2 | Nikhil |
| 3 | Rama |
| 4 | Vidyut |
| 5 | Pavan |
+-----+----------+
5 rows in set (0.00 sec)

mysql> select * from timesheet;
+------+--------+------------+
| eid | no_hrs | cdate |
+------+--------+------------+
| 1 | 100 | 2011-06-30 |
| 2 | 105 | 2011-06-30 |
| 3 | 90 | 2011-06-30 |
| 4 | 85 | 2011-06-30 |
| 5 | 110 | 2011-06-30 |
+------+--------+------------+
5 rows in set (0.00 sec)

mysql> select * from hr_rate;
+------+---------+
| eid | hr_rate |
+------+---------+
| 1 | 50 |
| 2 | 60 |
| 3 | 40 |
| 4 | 30 |
| 5 | 80 |
+------+---------+
5 rows in set (0.00 sec)

mysql> select Employee.ename,timesheet.no_hrs,hr_rate.hr_rate,timesheet.no_hrs*hr_rate.hr_rate as TOTAL
-> from Employee inner join timesheet using (eid)
-> inner join hr_rate using (eid)
-> where DAY(timesheet.cdate) = DAY(LAST_DAY(CURDATE()));
+----------+--------+---------+-------+
| ename | no_hrs | hr_rate | TOTAL |
+----------+--------+---------+-------+
| Abhishek | 100 | 50 | 5000 |
| Nikhil | 105 | 60 | 6300 |
| Rama | 90 | 40 | 3600 |
| Vidyut | 85 | 30 | 2550 |
| Pavan | 110 | 80 | 8800 |
+----------+--------+---------+-------+
5 rows in set (0.00 sec)

Comment hidden because of low score. Click to expand.
0

understanding the question should have saved u a lot of time.

Name:

Writing Code? Surround your code with {{{ and }}} to preserve whitespace.

Books

is a comprehensive book on getting a job at a top tech company, while focuses on dev interviews and does this for PMs.

Videos

CareerCup's interview videos give you a real-life look at technical interviews. In these unscripted videos, watch how other candidates handle tough questions and how the interviewer thinks about their performance.