CareerCup

  • Questions
  • Forum
  • Resume Tips
  • RSS
  • Sign In

Amazon Interview Question for Analysts






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

- Ayad Barsoum January 02, 2012 | Flag Reply
Comment hidden because of low score. Click to expand.
0
of 0 votes

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

- Zhijing78 April 05, 2013 | Flag
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)

- praveen May 14, 2012 | Flag Reply
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

- Amit January 31, 2011 | Flag Reply
Comment hidden because of low score. Click to expand.
0
of 0 votes

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

- Anon... April 27, 2012 | Flag
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

- Swathi January 31, 2011 | Flag Reply
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)

- AB February 12, 2011 | Flag Reply
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

- Anonymous February 18, 2011 | Flag Reply
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)

- thomas@zh-wang.me April 04, 2011 | Flag Reply
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

- Meera May 27, 2011 | Flag Reply
Comment hidden because of low score. Click to expand.
0
of 0 votes

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

- Anonymous May 27, 2011 | Flag
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

- finance_oracle_support March 04, 2012 | Flag Reply
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

- bhat.86 March 04, 2013 | Flag Reply
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
;

- aileen April 20, 2013 | Flag Reply
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

- Sai June 20, 2013 | Flag Reply
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

- Sai June 20, 2013 | Flag Reply
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

- connyr July 09, 2013 | Flag Reply
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

- M.A.S.Naidu February 20, 2015 | Flag Reply
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

- M.A.S.Naidu February 20, 2015 | Flag Reply
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

- KPK September 20, 2015 | Flag Reply
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)

- Abhishek June 05, 2011 | Flag Reply
Comment hidden because of low score. Click to expand.
0
of 0 votes

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

- anon August 21, 2011 | Flag


Add a Comment
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.

Learn More

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.

Learn More

Resume Review

Most engineers make critical mistakes on their resumes -- we can fix your resume with our custom resume review service. And, we use fellow engineers as our resume reviewers, so you can be sure that we "get" what you're saying.

Learn More

Mock Interviews

Our Mock Interviews will be conducted "in character" just like a real interview, and can focus on whatever topics you want. All our interviewers have worked for Microsoft, Google or Amazon, you know you'll get a true-to-life experience.

Learn More