-
data team: DSA and data science questions; algorithms and data structures questions with attention to complexity and possible optimizations.
-
describe the principles of object-oriented programming
-
design an API for an elevator.
-
design a cache
-
design question for some hypothetical Task object. write the algorithm and design all the classes required to complete all the tasks, subtasks, and their dependent tasks.
-
describe a particularly difficult concurrency problem you have faced and you solved it?
-
difference between cloud (computing) and virtualization. (virtualization is a virtualized simulation of a device or resource, e.g. storage, network, memory; cloud is shared computing resources, software, or data are delivered as a service and on-demand through the Internet.)
-
explain server caching/sessions
-
given a log of pages clicked on website by users (sorted by timestamp), find the top ten most clicked 3 page sequence. A 3 page sequence, is a sequence of 3 pages clicked by the same user in successive order.
-
how does garbage collection work in .NET?
-
implement a LRU cache
-
implement an infinite/large sized Tic Tac Toe game? how to check for win conditions?
-
optimize a memory situation, e.g. millions lines of data needs to be read into a server.
-
parse json and store the result in a csv file
-
string and array manipulations, minesweeper game, LRU Cache, Blackjack game, trees
-
scale of numbers
Number |
Name |
Symbol |
Description |
1,000,000,000,000,000,000,000,000 |
yotta |
Y |
Septillion |
1,000,000,000,000,000,000,000 |
zetta |
Z |
Sextillion |
1,000,000,000,000,000,000 |
exa |
E |
Quintillion |
1,000,000,000,000,000 |
peta |
P |
Quadrillion |
1,000,000,000,000 |
tera |
T |
Trillion |
1,000,000,000 |
giga |
G |
Billion |
1,000,000 |
mega |
M |
Million |
1,000 |
kilo |
k |
Thousand |
100 |
hecto |
h |
Hundred |
10 |
deca |
da |
Ten |
-
what are the four pillars of Object Oriented Programming? (Inheritance, Abstraction, Polymorphism, Encapsulation)
-
what is difference between null and undefined ?
-
what is JSON ? how to read and write ? (key-value pair; use stream/text reader/writer to deserialize/serialize)
-
write an elevator controller
-
collation is defined to specify the sort order in a table. 3 types (case in/sensitive, binary)
-
common table expression (CTE), as a temp query/view
WITH foo_cte AS (
SELECT a, b FROM [foo_table]
) --, bar_cte AS (
-- SELECT c, d FROM [bar_table]
),
SELECT * FROM [foo_cte] -- JOIN [bar_cte] ON ...
-
find largest/smallest
-
standard query in the book
SELECT * FROM employee
WHERE (department, salary) IN (
SELECT department, MAX(salary)
FROM employee
GROUP BY department
)
ORDER BY department;
-
using WITH
(analytic and window functions)
WITH ranked_employees AS (
SELECT ROW_NUMBER() OVER (
PARTITION BY department ORDER BY salary DESC
) AS rn, *
FROM employee
)
SELECT * FROM ranked_employees
WHERE rn = 1
ORDER BY department;
-
postgreSQL specific
SELECT DISTINCT ON (department) *
FROM employee
ORDER BY department, salary DESC;
-
find duplicate rows
SELECT col_name, COUNT(*) count FROM table GROUP BY col_name HAVING count > 1
SELECT DISTINCT col_name, count(col_name) FROM table GROUP BY col_name
-
delete duplicate rows
WITH cte_rows AS (
SELECT ROW_NUMBER()
OVER (PARTITION BY Col1, Col2 ORDER BY (SELECT 0)) RN
FROM #MyTable)
DELETE FROM cte_rows
WHERE RN > 1;
or on MySQL (without CTE
or PARTITION BY
clause)
-- to keep highest id
DELETE t1 FROM table t1, table t2
WHERE t1.id < t2.id AND t1.col_name == t2.col_name
or
-- to keep lowest id
DELETE t1 FROM table t1, table t2
WHERE t1.id < t2.id AND t1.col_name == t2.col_name
or
CREATE TABLE tmp_table LIKE src_table;
INSERT INTO tmp_table(id) SELECT MAX(id) as id
FROM src_table
GROUP BY field1, field2
HAVING COUNT(*) > 1
DELETE FROM src_table
WHERE id IN (SELECT id FROM tmp_table)
DROP TABLE tmp_table
or on other system allows deleting from the same table
DELETE FROM src_table src
WHERE src.id IN (
SELECT MAX(dup.id)
FROM src_table as dup
GROUP BY dup.field1, dup.field2
HAVING COUNT(*) > 1 )
-
remove enum value (postgreSQL)
-
Rename existing type
ALTER TYPE some_enum_type RENAME TO some_enum_type_old;
-
Create a new type (without the enum value to be removed)
-
Change all existing columns which use the old type to use the new one.
DO $$
DECLARE
column_data record;
table_name varchar(255);
column_name varchar(255);
BEGIN
FOR column_data IN
SELECT cols.table_name, cols.column_name
FROM information_schema.columns cols
WHERE udt_name = 'some_enum_type_old'
LOOP
table_name := column_data.table_name;
column_name := column_data.column_name;
EXECUTE format(
'
ALTER TABLE %s
ALTER COLUMN %s
TYPE some_enum_type
USING %s::text::some_enum_type;
',
table_name, column_name, column_name
);
END LOOP;
END $$;
-
Delete the old type.
DROP TYPE some_enum_type_old;
See this blog
-
data types
Type |
B |
Minimum Value |
Maximum Value |
Notes |
TINYINT |
1 |
-128 |
127 |
|
SMALLINT |
2 |
-32,768 |
32,767 |
32 K |
MEDIUMINT |
3 |
-8,388,608 |
8,388,607 |
8+ millions |
INT |
4 |
-2,147,483,648 |
2,147,483,647 |
2+ billions |
BIGINT |
8 |
-9,223,372,036,854,775,808 |
9,223,372,036,854,775,807 |
9+ quintillion |
-
difference between clustered and a non-clustered index? (clustered index reorder the row as physically stored, the leaf nodes contain the data pages)
-
difference between primary key and unique key? (PR constraint is a unique identifier for each row, it creates clustered index and does not allow NULL)
-
difference between DELETE
and TRUNCATE
commands?
-
difference between HAVING
clause and WHERE
clause? (HAVING
used only with the GROUP BY)
-
explain ACID: 4 properties to qualify a transaction (which is a sequence of operations performed as a single logical unit of work)
-
Atomicity: as atomic unit of work, either all or none of modifications are perform.
-
Consistency: all data in a consistent state; all rules must be applied to maintain all data integrity.
-
Isolation: modifications must be isolated from modifications made by any other concurrent transaction.
-
Durability: modifications persist permanently in the system, even in the event of system failure.
-
Lock modes: Intent shared (IS), Intent exclusive (IX), Shared with intent exclusive (SIX), Intent update (IU), Shared intent update (SIU), Update intent exclusive (UIX)
-
see https://technet.microsoft.com/en-us/library/jj856598(v=sql.110).aspx
Isolation level |
Dirty read |
Nonrepeatable read |
Phantom |
Read uncommitted |
Yes |
Yes |
Yes |
Read committed |
No |
Yes |
Yes |
Repeatable read |
No |
No |
Yes |
Snapshot |
No |
No |
No |
Serializable |
No |
No |
No |
-
explain inner vs outer joins
-
explain normalization vs. de-nomarlization
-
explain NOLOCK hint, when to or not to use. (READ UNCOMMITTED/dirty data by engine faster, applicable to SELECT only)
-
how to check locks in database ? (sp_lock
)
-
how to get a list of triggers ? types of triggers ? (INSERT
, DELETE
, UPDATE
, INSTEAD OF
)
select * from sys.objects where type=’tr’
-
use nested SQL select statements to determine a value in table A from information in table B
-
use SQL Server ROW_NUMBER()
SELECT ROW_NUMBER() OVER(PARTITION BY city ORDER BY age) AS rank, city, age
FROM People
-
use COALESCE to return first non-null expression within the arguments.
-
use methods to protect against SQL injection attack:
-
Use Parameters for Stored Procedures
-
Use Parameter collection with Dynamic SQL
-
In like clause, user escape characters
-
Filtering input parameters
-
adding and removing nodes from a ternary tree
-
binary search tree vs balanced BST vs heap (binary heap, min-/max-heap)
-
check if a binary tree a BST (binary search tree)
-
check if a tree is not a graph (similar data structure but have cyclic path)
-
check if one tree contains another tree
-
compare the node in an unsorted d-tree
-
create a tree, e.g. insert and delete in a trinary tree
-
find the common ancestor of two nodes
-
find the Least Common Ancestor given two nodes of a binary tree. The nodes each have a reference to their parent node and you do not have the root node of the tree.
-
find LCA of a Binary Tree and making it efficient.
-
given a binary tree and a sum, determine if the tree has a root-to-leaf path such that adding up all the values along the path equals the given sum. Follow-up: find all the paths
-
lowest common ancestor in a BT
-
questions about trees with unconventional structures
-
search through a binary search tree, what is the worst-case big-O complexity?
-
traversal methods
-
breadth-first
// A(root)
// / \
// B C
// / \ \
// D E F
// / \ /
// G H I
//
breadthFirst(root) {
let node = root;
let nodeList = []; // node list
let queue = [];
queue.push(node); // push root into queue
while (queue.length > 0) {
node = queue.shift(); // dequeue the node
nodeList.push(node); // append node to the list
if (node.left) queue.push(node.left);
if (node.right) queue.push(node.right);
}
return nodeList;
}
-
depth-first (pre-order):
- top-down order
- e.g. reading hierarchical document in natural oder (chapters/sections/... in a book)
- e.g. prefix expression tree
"(a+b)*c" => * + a b c
(Polish Notation) in arithmetic parser
// A(root)
// / \
// B G
// / \ \
// C D H
// / \ /
// E F I
//
depthFirstPreOrder(node, nodeList) {
if (node) {
nodeList.push(node);
depthFirstPreOrder(node.left, nodeList);
depthFirstPreOrder(node.right, nodeList);
}
}
depthFirstPreOrderIterative(root) {
let node = root;
let nodeList = [];
let stack = [];
stack.push(node);
while (stack.length > 0) {
node = stack.pop(); // pop the node from stack
nodeList.push(node);
if (node.right) stack.push(node.right);
if (node.left) stack.push(node.left);
}
return nodeList;
}
-
depth-first (in-order)
- e.g. binary search tree, or infix
(a+b)*c
// F(root)
// / \
// B G
// / \ \
// A D I
// / \ /
// C E H
//
depthFirstInOrder(node, nodeList) {
if (node) {
depthFirstInOrder(node.left, nodeList);
nodeList.push(node);
depthFirstInOrder(node.right, nodeList);
}
}
depthFirstInOrderIterative(root) {
let node = root
let nodeList = []
let stack = []
while (node || stack.length > 0) {
if (node) {
stack.push(node)
node = node.left
} else {
node = stack.pop() // pop top node from the stack
nodeList.push(node)
node = node.right
}
}
return nodeList
}
-
depth-first (post-order)
- bottom-up order, visiting all leaves before their parent
- e.g. postfix expression tree
"(a+b)*c" => a b + c *
(Reverse Polish Notation) in arithmetic parser
// I(root)
// / \
// E H
// / \ \
// A D G
// / \ /
// B C F
//
depthFirstPostOrder(node, nodeList) {
if (node) {
depthFirstPostOrder(node.left, nodeList);
depthFirstPostOrder(node.right, nodeList);
nodeList.push(node);
}
}
depthFirstPostOrderIterative(root) {
let nodeList = []
let stack = []
stack.push(root)
while (stack.length > 0) {
let node = stack.pop()
nodeList.unshift() // insert the node in reversed order
if (node.left) stack.push(node.left)
if (node.right) stack.push(node.right)
}
return nodeList
}
-
tree construction
-
create from pre-order
fromPreOrder(inputs) {
var node = null;
var size = inputs.length;
var half = parseInt(size / 2) + 1;
if (size > 0) {
node = {}
node.value = inputs[0]; // first item is the root node
if (half > 1) {
node.left = fromPreOrder(inputs.slice(1, half));
}
if (half < size) {
node.right = fromPreOrder(inputs.slice(half, size));
}
}
return node;
}
-
create from post-order
fromPostOrder(inputs) {
var node = null;
var size = inputs.length;
var half = parseInt(size / 2);
if (size > 0) {
node = {}
node.value = inputs[size-1]; // last item is the root node
if (half > 0) {
node.left = fromPostOrder(inputs.slice(0, half));
}
if (half < size-1) {
node.right = fromPostOrder(inputs.slice(half, size-1));
}
}
return node;
}
-
create from in-order
fromInOrder(inputs) {
var node = null;
var size = inputs.length;
var half = parseInt(size / 2);
if (size > 0) {
node = {}
node.value = inputs[half]; // middle item is the root node
if (half > 0) {
node.left = fromInOrder(inputs.slice(0, half));
}
if (half < size-1) {
node.right = fromInOrder(inputs.slice(half+1, size));
}
}
return node;
}