Basics of Python Programming
a. Lists, Tuples, Dictionaries, Conditionals, Loops, etc…
b. Data Structures & Algorithms
d. NumPy Arrays: https://lnkd.in/geeFePh
c. Regex: https://lnkd.in/gzUahNV
Practice Coding Challenges
a. Hacker Rank: https://lnkd.in/gEufBUu
b. Codeacademy: https://lnkd.in/gGQ7cuv
c. LeetCode: https://leetcode.com/
Data Manipulation
a. Pandas: https://lnkd.in/gxSgfuQ
b. Pandas Cheatsheet: https://lnkd.in/gfAdcpw
c. SQLAlchemy: https://lnkd.in/gjvbm7h
Data Visualization
a. Matplotlib: https://lnkd.in/g_3fx_6
b. Seaborn: https://lnkd.in/gih7hqz
c. Plotly: https://lnkd.in/gBYBMXc
d. Python Graph Gallery: https://lnkd.in/gdGe-ef
Machine Learning / Deep Learning
a. Skcikit-Learn Tutorial: https://lnkd.in/gT5nNwS
b. Deep Learning Tutorial: https://lnkd.in/gHKWM5m
c. Kaggle Kernels: https://lnkd.in/e_VcNpk
d. Kaggle Competitions: https://lnkd.in/epb9c8N
Top Categories
SQL Interview Questions
Our schema
We’ll be going through six questions covering topics like query performance, joins, and SQL injection. They’ll refer to the same database for cakes, customers, and orders at a bakery. Here’s the schema:
CREATE TABLE cakes (
cake_id INT NOT NULL AUTO_INCREMENT PRIMARY KEY,
flavor VARCHAR(100) NOT NULL
);
CREATE TABLE customers (
customer_id INT NOT NULL AUTO_INCREMENT PRIMARY KEY,
first_name VARCHAR(100) NOT NULL,
last_name VARCHAR(100) NOT NULL,
phone VARCHAR(15),
street_address VARCHAR(255),
city VARCHAR(255),
zip_code VARCHAR(5),
referrer_id INT,
FOREIGN KEY (referrer_id) REFERENCES customers (customer_id)
);
CREATE TABLE orders (
order_id INT NOT NULL AUTO_INCREMENT PRIMARY KEY,
cake_id INT NOT NULL,
customer_id INT,
pickup_date DATE NOT NULL,
FOREIGN KEY (cake_id) REFERENCES cakes (cake_id),
FOREIGN KEY (customer_id) REFERENCES customers (customer_id)
);SQL
We’ll be using MySQL for consistency.
Want to get set up with sample data? Here’s how to get four cakes, a million customers, and a million orders:
In your terminal, download our script and start up MySQL:
$ curl -O https://static.interviewcake.com/bakery_schema_and_data.sql && mysql.server start && mysql -u rootThen run our script to set up the BAKERY database and insert data:
> source bakery_schema_and_data.sql;If you want to come back to the data again and you already downloaded and ran the script:
- In terminal, start up MySQL: mysql.server start && mysql -u root
- In the MySQL shell, use the bakery database: USE BAKERY;
Alright, let’s get started.
How can we make this query faster?
We want the order ID of every order in January and February, 2017. This is the query we’ve got so far:
SELECT order_id FROM orders WHERE DATEDIFF(orders.pickup_date, '2017-03-01') < 0;
-- 161314 rows in set (0.25 sec)SQL
How can we get the nth highest number of orders a single customer has?
We made a view of the number of orders each customer has:
CREATE VIEW customer_order_counts AS
SELECT customers.customer_id, first_name, count(orders.customer_id) AS order_count
FROM customers LEFT OUTER JOIN orders
ON (customers.customer_id = orders.customer_id)
GROUP BY customers.customer_id;SQL
So for example, Nancy has 3 orders:
SELECT * FROM customer_order_counts ORDER BY RAND() LIMIT 1;
/*
+-------------+------------+-------------+
| customer_id | first_name | order_count |
+-------------+------------+-------------+
| 9118 | Nancy | 3 |
+-------------+------------+-------------+
*/SQL
What ways can we use wildcard characters in LIKEclauses?
Now how can we make this query faster?
We’re mailing a promotion to all our customers named Sam who live in Dover. Since some customers go by names that aren’t exactly Sam, like Samuel or Sammy, we use names like Sam. Here’s how we find them:
SELECT first_name, last_name, street_address, city, zip_code FROM customers
WHERE first_name LIKE '%sam%' AND city = 'Dover';
-- 1072 rows in set (0.42 sec)SQL
That’s pretty slow. How can we speed it up?
Kirosdsi 