Amazon Interview Question for Software Engineer in Tests


Country: India
Interview Type: In-Person




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

create table A (a1 int,a2 int)
insert into A values(1,3),(1,3),(2,4),(3,5),(3,5),(3,5);


WITH CTE AS(
SELECT *,ROW_NUMBER() OVER(PARTITION BY A1 ORDER BY A2 DESC) AS TAB_ROW
FROM A)
DELETE FROM CTE WHERE TAB_ROW=2

- Ayushi Ag June 08, 2014 | Flag Reply
Comment hidden because of low score. Click to expand.
-1
of 1 vote

It should be

DELETE FROM CTE WHERE TAB_ROW<>1

otherwise only the second entry will be deleted and not the thirs one like in case of 3 5

- Anonymous January 26, 2015 | Flag
Comment hidden because of low score. Click to expand.
0
of 0 votes

oh sorry never mind..they just want one duplicate row to be delted..u r right

- Anonymous January 26, 2015 | Flag
Comment hidden because of low score. Click to expand.
2
of 2 vote

DELETE FROM TBL WHERE ROWID IN (SELECT MIN(ROWID) FROM TBL GROUP BY A,B HAVING COUNT(*) > 1);

- Satya July 09, 2014 | Flag Reply
Comment hidden because of low score. Click to expand.
0
of 0 vote

//Approach 1
1. check whether the data is huge, ie if the size of the input file more than the JVM heap size,
split the input into chunks , which can fit into memory
2. Sort the data in the chunk using merge sort (optional)

3. use the code to remove the duplicates.

public void approach1()
	{
		try {
			BufferedReader br=new BufferedReader(new FileReader(new File("filepath")));
			
			try {
				Set<String> set=new HashSet<String>();
				String line=null;
				while((line=br.readLine())!=null)
				{
					set.add(line);
				}
				
				
				
				
			} catch (IOException e) {
				
				e.printStackTrace();
			}
		} catch (FileNotFoundException e) {
			
			e.printStackTrace();
		}
	}

- lal April 30, 2014 | Flag Reply
Comment hidden because of low score. Click to expand.
1
of 1 vote

won't this delete all the duplicates and not just 1 of each ?

- iamkake April 30, 2014 | Flag
Comment hidden because of low score. Click to expand.
0
of 0 vote

- Create a mapping between entry (string?) and int
- Go through the list and for each increment the int by 1 so at the end you have a count of everything
- Go through the mapping you made and for each if there is only 1, print it out, if there is more than 1 print it out the number in the mapping minus 1

public string[] filter(string[] everything)
{
 if (everything == null || everything.length < 2) { return everything; }

 HashMap<string,int> counts = new HashMap<string,int>();
 for (int i = 0 ; i < everything.length; i++)
 {
    if (counts.containsKey(everything[i])) 
     {
        counts.put(everything[i],counts.get(everything[i]) + 1);
     }
    else {
       counts.put(everything[i], 1);
    }
 }

List<string> finalList = new ArrayList<string>();
 for (Map.Entry<string, int> entry : map.entrySet()) {
      int times = entry.getValue();
      if (times == 1) { 
         finalList.add(entry.getKey());
      }
      else {
        for (int i = 0; i < times - 1; i++)
         {
              finalList.add(entry.getKey());
         }
      }
  }

  string[] arrayFinal = new string[finalList.size()];
  finalList.toArray(arrayFinal);
  return arrayFinal;

}

- iamkake April 30, 2014 | Flag Reply
Comment hidden because of low score. Click to expand.
0
of 0 vote

Is this problem driven by any real world problem? I don't understand the requirement of deleting just one duplicate record.

- Learner April 30, 2014 | Flag Reply
Comment hidden because of low score. Click to expand.
0
of 0 votes

Do you think that most interview questions are driven by real world problems?

- iamkake April 30, 2014 | Flag
Comment hidden because of low score. Click to expand.
0
of 0 vote

Sorry frnds ... BUT it was a SQL question :)

- Rahul Sharma April 30, 2014 | Flag Reply
Comment hidden because of low score. Click to expand.
2
of 2 votes

and the only column is a1 and a2??

- iamkake April 30, 2014 | Flag
Comment hidden because of low score. Click to expand.
0
of 0 votes

right

- Rahul Sharma May 01, 2014 | Flag
Comment hidden because of low score. Click to expand.
0
of 0 vote

First of all why n the sample output we have
3 5
3 5
I guess there should be only one of them.

This is classic duplicate removal algorithm with a little tweak.
Use a HashMap ..
Iteration over the relational structure (2D array in fact)
1. Create key as "col1+col2+..+coln" - a string
2. Check if the key is already there in the map
3. If yes - delete the current row (this can be done in multiple way - first step could be just marking e.g keeping track of the row to be deleted or setting all values as null.
in second second actually remove that row by not coping such a marked row)
4. if No - add this key in the map
5. do same till all rows are visited.

Then remove the marked rows as mentioned above as second step.

- DH May 01, 2014 | Flag Reply
Comment hidden because of low score. Click to expand.
0
of 0 vote

Some more simplified...

import java.io.*;
import java.util.Arrays;

public class Task3 {


	public static void main(String args[])
	{
			
		
		String m[] = {"13","23","13","24","23", "13", "24","24"};
		
		String n[] = new String[10];
		
		n[0] = "0";
		Arrays.sort(m);
		
		
		for (int i = 0; i <m.length-1; i++) 
		{
			
					
		    if ((m[i] == m[i+1])  )
		    {

		    	n[i] = m[i]; //copy the elements if the next element is same as previous!
		    	
		    	//System.out.println("duplicate item "+m[i+1]+" at Location"+(i+1) );
		    		
		   }
		    
		  //taking the last element!
			if(i+1 == m.length)
			{
				n[i+1]=m[i+1];
			}
		    
		    continue;
		}
		
		
		
		//printing the elements which removes one duplicate element!
		for(int j=0;j<n.length;j++)
		{
			if(n[j]!=null)
			{
				System.out.println(n[j]);
			}
		}
		 
	}
}

- Madhu May 01, 2014 | Flag Reply
Comment hidden because of low score. Click to expand.
0
of 4 vote

SELECT "a1", "a2"
FROM TableName
GROUP BY "a1", "a2"
HAVING COUNT(*) > 1 UNION
SELECT "a1", "a2"
FROM TableName
GROUP BY "a1", "a2"
HAVING COUNT(*) = 1

- Anonymous May 01, 2014 | Flag Reply
Comment hidden because of low score. Click to expand.
1
of 1 vote

Nope. The group by acts like a 'distinct' operator and you will only get back 1 row for each. Having 2 queries is redundant since your are really doing the same thing whether or not count == 1 or count > 1.

This is pretty much what you wrote, which is note correct.

SELECT a1, a2 
FROM tx 
GROUP BY a1, a2

- nezro May 14, 2014 | Flag
Comment hidden because of low score. Click to expand.
0
of 0 votes

To delete only one duplicate row as asked in qus:

delete
from Table
where rowid in (SELECT rowid FROM Table group by a1,a2 having count(1) >1)

- PKT October 04, 2014 | Flag
Comment hidden because of low score. Click to expand.
0
of 0 vote

public static void removeDuplicate() {
		String[] elem = { "1 3", "1 3", "2 4", "3 5", "3 5", "3 5", "1 1",
				"1 1", "1 1", "1 1" };

		Map<String, Integer> map = new HashMap<String, Integer>();

		for (String str : elem) {
			if (map.containsKey(str))
				map.put(str, map.get(str) + 1);
			else
				map.put(str, 1);
		}

		for (String str : elem) {
			if (map.containsKey(str)) {
				if (map.get(str) == 2) {
					System.out.println(str);
					map.put(str, null);
				} else {
					System.out.println(str);
					map.put(str, map.get(str) - 1);
				}
			}
		}

	}

- Snigda May 01, 2014 | Flag Reply
Comment hidden because of low score. Click to expand.
0
of 0 votes

there was one mistake in able code
here is the working code
public static void removeDuplicate() {
		String[] elem = { "1 3", "1 3", "2 4", "3 5", "3 5", "3 5", "1 1",
				"1 1", "1 1", "1 1" };

		Map<String, Integer> map = new HashMap<String, Integer>();

		for (String str : elem) {
			if (map.containsKey(str))
				map.put(str, map.get(str) + 1);
			else
				map.put(str, 1);
		}

		for (String str : elem) {
			if (map.containsKey(str)) {
				if (map.get(str) == 2) {
					System.out.println(str);
					map.remove(str);
				} else {
					System.out.println(str);
					map.put(str, map.get(str) - 1);
				}
			}
		}

	}

- Snigda May 01, 2014 | Flag
Comment hidden because of low score. Click to expand.
0
of 0 vote

SQL

SELECT a1, a2 from (
SELECT a1, a2, generate_series(1, case when count(*) > 1 then count(*) - 1 else 1 end)
FROM (VALUES 
(1, 3),
(1, 3), 
(2, 4), 
(3, 5), 
(3, 5), 
(3, 5)) as t(a1, a2)
GROUP BY a1, a2
) as t1(a1, a2, c)

- Anonymous May 02, 2014 | Flag Reply
Comment hidden because of low score. Click to expand.
0
of 0 vote

String[] inputs={"1 3","1 3","2 4","3 5","3 5","3 5","1 1","1 1","1 1","1 1"};
		Set<String> deletedElements=new HashSet<String>();
		for(int i=1;i<inputs.length;i++)
		{
			for(int j=i-1;j>=0;j--)
			{
				if(inputs[i]!=null&&inputs[i].equals(inputs[j])&&!deletedElements.contains(inputs[j]))
				{
					for(int k=j;k<inputs.length-1;k++)
					{
						inputs[k]=inputs[k+1];
						if(k+1==inputs.length-1)
							inputs[k+1]=null;
					}
					deletedElements.add(inputs[j]);
				}
			}
		}

- Vivek Grewal May 04, 2014 | Flag Reply
Comment hidden because of low score. Click to expand.
0
of 0 vote

You want to count the rows and restart the count everytime the rows change.
Filter out tgt==2 because you will only have tgt == 2 when you have a row duplicated at least once.


in oracle:

SELECT A1,A2 
	FROM
	(
	SELECT 
	  A1
	, A2
	,ROW_NUMBER() OVER (PARTITION BY A1,A2 ORDER BY A1,A2) tgt
	FROM TX
	)
WHERE tgt  <> 2

- nezro May 14, 2014 | Flag Reply
Comment hidden because of low score. Click to expand.
0
of 0 vote

DELETE FROM A 
  WHERE ROWID IN 
     (SELECT ROWID FROM 
            (SELECT a1,a2,ROWID,ROW_number() over (partition by a1 order by a2 desc) rn 
                FROM  A) x where x.rn=2);

- narasimha June 13, 2014 | Flag Reply
Comment hidden because of low score. Click to expand.
0
of 0 vote

public class RemoveOneDuplicate {
public static void main(String[] args) {

String[] elem={"1 3","1 3","2 4","3 5","3 5","3 5","1 1","1 1","1 1","1 1"};

List<String> al=new ArrayList<String>(); // to get the final string

al.addAll(Arrays.asList(elem));
System.out.println("Original array list: "+al);

HashMap<String,Integer> hm=new HashMap<String, Integer>();

for(int j=0;j<elem.length;j++)
{
if(hm.containsKey(elem[j]))
{
int k=hm.get(elem[j]);
k=k+1;
hm.remove(elem[j]);
hm.put(elem[j],k);

}
else
{
hm.put(elem[j], 1);

}
}

Set s=hm.entrySet();
Iterator it=s.iterator();

while(it.hasNext())
{
Map.Entry pairs=(Map.Entry)it.next();
//System.out.println(pairs.getKey()+"----------------"+pairs.getValue());

int n=((Integer) pairs.getValue()).intValue();
if(n >= 2)
{
n--;
pairs.setValue(n);
al.remove((String) pairs.getKey());
}
//System.out.println("### "+s);

}
System.out.println("Hashmap : "+hm);
Object obj[]=al.toArray();
System.out.println("FINAL STRING"+Arrays.toString(obj));
}
}

- dee August 24, 2014 | Flag Reply
Comment hidden because of low score. Click to expand.
0
of 0 vote

SELECT S.a1, S.a2 FROM (SELECT a1, rank FROM (SELECT a1, @rank:=@rank+1 AS rank FROM table, (SELECT @rank:=0)r AS R) AS S) JOIN (SELECT a1, rank FROM (SELECT a1, @rank:=@rank+1 AS rank FROM table, (SELECT @rank:=0)r AS U) AS T) ON S.rank = T.rank+1 AND S.a1 = T.a1 AND S.a2=T.a2 UNION SELECT a1, a2 FROM table GROUP BY a1, a2 HAVING COUNT(*)=1

---------------------------------

The idea is first put a rank id number for each row:
rank a1, a2
1, 1, 3
2, 1, 3
3, 2, 4
......

Then self join the table ON table1.rank=table2.rank+1, table1.a1 = table2.a1, table1.a2 = table2.a2. This will just remove 1 duplicate for those (a1, a2) with more than 1 count.

Finally, Union the above results with rows only contain no duplicate pairs (a1, a2)

- andycuisong September 18, 2014 | Flag Reply
Comment hidden because of low score. Click to expand.
0
of 0 vote

Considering this is stored in a table named TAB in database:

DELETE FROM TAB
WHERE ROWID IN ( SELECT RID
FROM (SELECT ROWID RID,
ROW_NUMBER() OVER (PARTITION BY
ID,NAME,SAL
ORDER BY NAME) RN
FROM TAB)
WHERE RN = 2 );

- mandarsg September 19, 2014 | Flag Reply
Comment hidden because of low score. Click to expand.
0
of 0 vote

delete from table where rowid not in (select max(rowid) from table group by a1)

- vijayedella September 21, 2014 | Flag Reply
Comment hidden because of low score. Click to expand.
0
of 0 vote

select a1, a2 from table_name
minus
(select a1, a2 from table_name
group by a1, a2
having count(*)>=2)

- jwei.aiesec.tju September 26, 2014 | Flag Reply
Comment hidden because of low score. Click to expand.
0
of 0 vote

this shld work.

with temp(rn,a1,a2)
as
(
select ROW_NUMBER() over(partition by a1,a2 order by id ) as rn, * from tab
)
delete from temp where rn>1

- mmayur October 03, 2014 | Flag Reply
Comment hidden because of low score. Click to expand.
0
of 0 vote

To delete only one duplicate row as asked in qus:
delete
from Table
where rowid in (SELECT rowid FROM Table group by a1,a2 having count(1) >1)



To show one duplicate less data
select *
from Table
where rowid not in (SELECT rowid FROM Table group by a1,a2 having count(1) >1)

- PKT October 04, 2014 | Flag Reply
Comment hidden because of low score. Click to expand.
0
of 0 vote

select colA, colB, row_number()over(partition by colA, colB order by rand())
from Table
except
select distinct colA, colB, 1
from Table
group by colA, colB
having COUNT(*) > 1

- tommyguc March 17, 2015 | Flag Reply
Comment hidden because of low score. Click to expand.
0
of 0 vote

{
select colA, colB, row_number()over(partition by colA, colB order by rand())
from Table
except
select distinct colA, colB, 1
from Table
group by colA, colB
having COUNT(*) > 1
}

- tommyguc March 17, 2015 | Flag Reply
Comment hidden because of low score. Click to expand.
0
of 0 vote

with temp_view as (
select *,ROW_NUMBER() over(partition by a1,a2 order by a2 desc) as row_no from test2
)
delete from temp_view where row_no<>1;

- saumya.wipro April 14, 2015 | Flag Reply
Comment hidden because of low score. Click to expand.
0
of 0 vote

delete from user where rowid in (select rowid from user group by name having count(*) > 1)

- Anonymous June 28, 2016 | Flag Reply
Comment hidden because of low score. Click to expand.
0
of 0 vote

1. Delete a1 from /*consider table name as row*/
row a1,
row a2
where a1.row = a2.row
and a1.row > a2.row

- Anonymous December 27, 2019 | Flag Reply
Comment hidden because of low score. Click to expand.
-1
of 3 vote

Try this code.It removes one duplicate exactly

import java.util.HashMap;
import java.util.Iterator;
import java.util.Map;
import java.util.Set;

public class Sample {
public static void main(String[] args) {
	String[] elem={"1 3","1 3","2 4","3 5","3 5","3 5","1 1","1 1","1 1","1 1"};
	int ml=1;
	HashMap<String,Integer> hm=new HashMap<String, Integer>();
	
	for(int j=0;j<elem.length;j++)
	{
		if(hm.containsKey(elem[j]))
		{
			System.out.println("inside for loop"+elem[j]);
		int k=	hm.get(elem[j]);
		k=k+1;
		hm.remove(elem[j]);
		hm.put(elem[j],k);
		System.out.println("inside for count of "+elem[j]+" :"+k);
		}
		else
		{
			hm.put(elem[j], 1);
			System.out.println("inside for loop else"+elem[j]);
		}
	}
	
		Iterator it=hm.entrySet().iterator();
		while(it.hasNext())
		{
		Map.Entry pairs=(Map.Entry)it.next();
		int n=Integer.parseInt(pairs.getValue().toString());
		System.out.println(pairs.getKey());
		for(int k=2;k<n;k++)
		System.out.println(pairs.getKey());
		it.remove();
		}
		}
}

- Nivethitha May 01, 2014 | Flag Reply
Comment hidden because of low score. Click to expand.
-1
of 1 vote

Awesome, works perfectly fine...

- Madhu May 01, 2014 | 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