yaQnJbJC

1# Facebook Infrastructure Data Scientist - Internship
2
3## Statistics
4* Hypothesis Testing
5* Statistical Inference
6* Distributions
7* Linear Regression
8* Logistic Regression
9* General Experimentation Practices
10* Predictive Analytics
11* ML
12
13## SQL
14
15* http://sqlzoo.net 
16* http://www.sqlcourse2.com/index.html
17* https://www.codecademy.com/learn/learn-sql
18
19## Python
20* Python nuances
21* A bit about R
22* Data Processing for structured/unstructured data
23* Data mining/parsing
24* Data Structures/Algorithms
25* Pandas
26* Numpy
27* String manipulation / Regex
28
29## Machine Learning
30* Machine Learning algorithms
31
32## Tips
33* 5-10 minutes per question
34* Sample Projects:
35     * https://code.facebook.com/posts/1676452492623525/connecting-the-world-with-better-maps/?hc_location=ufi
36     * https://research.fb.com/facebook-disaster-maps-methodology/
37     * https://newsroom.fb.com/news/2016/02/introducing-the-telecom-infra-project/
38
39
40----
41## SQL
42
43### SELECT
44
45* Output from SELECT is always a grid with a number of rows and columns
46* ``` SELECT name, population FROM bbc WHERE region="America"```
47* SELECT statement determines which columns to retrieve
48* WHERE is the condition on the data
49* SELECT can have data from multiple tables using JOIN or UNION
50* SELECT statements may be nested
51* Output from one SELECT can be used as an INSERT ```INSERT ... SELECT```
52* Can aggregate values using GROUP BY
53
54#### CONCAT
55
56* Putting two or more strings together using CONCAT
57* ``` SELECT CONCAT(region,name) AS CombinedGroup FROM bbc``` just creates one combined group
58
59#### LIKE
60* LIKE command allows wildcards (Pattern Matching)
61* % is to match a string whereas _ to match any character
62* ``` SELECT name FROM bbc WHERE name LIKE 'Z%'```
63
64#### UNION
65* We list a number of SELECT statements separated by UNION keywords
66* Ensure you have the same number of columns in each of the SELECT statements
67* ``` SELECT name FROM bbc WHERE name LIKE 'D%' UNION SELECT name FROM actor WHERE name LIKE 'D%' ```
68
69#### APOSTROPHE ESCAPING
70* ``` SELECT * FROM bbc WHERE name = 'Cote d''Ivoire' ```
71* We use another ' as an escape for a pre-existing ' in our string so 'Tom's' -> 'Tom''s'
72
73#### FULL TEXT SEARCH
74* Using the LIKE operator, it checks if the string exists at all
75* ``` SELECT name FROM bbc WHERE name LIKE '%and%'``` eg: Andola, Finland, Trinidad and Tobago
76
77#### AGGREGATE FUNCTIONS and GROUPBY
78* When using an aggregate function like SUM then we need to follow with a GROUPBY
79```SQL 
80SELECT region, SUM(population) AS x FROM bbc GROUP BY region 
81```
82
83#### CREATE TABLES
84* Using a self join
85```SQL
86CREATE TABLE employee(
87  employee_id INTEGER PRIMARY KEY,
88  first_name VARCHAR(10),
89  dept_code VARCHAR(10),
90  manager_id INTEGER REFERENCES employee);
91INSERT INTO employee VALUES (1,'Robin','Eng',NULL);
92INSERT INTO employee VALUES (2,'Jon','SoC',1);
93INSERT INTO employee VALUES (3,'Andrew','SoC',2);
94INSERT INTO employee VALUES (4,'Alison','SoC',2);
95SELECT w.first_name as worker, b.first_name as boss
96 FROM employee w, employee b
97WHERE w.manager_id = b.employee_id
98```
99
100* Column names with spaces use backticks `
101
102```SQL
103CREATE TABLE SpaceMonster(`Account Balance` INT);
104INSERT INTO SpaceMonster VALUES (42);
105INSERT INTO SpaceMonster VALUES (100);
106SELECT `Account Balance` FROM SpaceMonster;
107```
108
109#### NULL
110* Missing or unknown data
111* Can use the phrase `IS NULL`
112
113
114### CREATE & DROP Tables
115
116#### CREATE a new Table
117```SQL
118CREATE TABLE t_test
119(a INTEGER PRIMARY KEY,
120b VARCHAR(10))
121```
122
123* VARCHAR(10) Up to 10 characters
124* CHAR(10) Fixed number of chars
125* INTEGER
126* DATE
127* FLOAT
128* TIMESTAMP
129
130#### DROP a table
131
132``` DROP TABLE t_test```
133* Problems if foreign key references
134
135#### Composite primary key
136* Composite key is made of more than just one field
137
138```SQL
139CREATE TABLE track(
140  album CHAR(10) NOT NULL,
141  dsk INTEGER NOT NULL,
142  posn INTEGER NOT NULL,
143  song VARCHAR(255),
144  PRIMARY KEY (album, dsk, posn)
145)
146```
147
148---
149## SQL Summarized
150
151### SELECT 
152
153```SQL
154SELECT [*|DISTINCT] columns FROM tables WHERE conditions GROUP BY column-list ORDER BY column-list ASC|DESC 
155```
156
157* ALL or DISTINCT determine whether to retrieve the records all of them or just unique ones
158* String matching using % if it starts with s `s%` if it has a word then `%word%`
159
160### AGGREGATE FUNCTIONS
161
162```SQL
163MIN
164MAX
165SUM
166AVG
167COUNT: Total number of values in a column
168COUNT(*): Number of rows in a table
169
170```
171
172* Aggregate functions compute against a returned column from `SELECT` statement
173
174Examples:
175
176```SQL
177SELECT AVG(salary) FROM employee;
178
179SELECT AVG(salary) FROM employee WHERE title=`Web Designer`;
180
181//Number of rows in employees tables
182SELECT COUNT(*) FROM employee;
183
184// USING LIKE
185
186SELECT avg(price)
187FROM items_ordered
188WHERE order_date LIKE '%Dec%';
189```
190
191* Whenever we do pattern matching instead of using = we use `LIKE`
192
193### GROUP BY
194
195* Gathers all the rows that contain data in specified columns and will allows aggregate functions to be performed on one or more columns
196
197Example:
198```SQL
199SELECT column1, SUM(column2)
200
201FROM "list-of-tables" GROUP BY "column-list";
202
203```
204
205```SQL
206// Maximum salary per dept
207
208SELECT max(salary), dept
209FROM employee
210GROUP BY dept;
211```
212
213Multiple grouping columns
214
215```SQL
216// Displaying last name too
217
218SELECT max(salary), dept, lastname
219FROM employee
220GROUP BY dept, lastname;
221
222
223SELECT COUNT(customerid), customerid FROM items_ordered GROUP BY customerid;
224```
225
226### HAVING
227
228* Conditions on the rows for each `GROUP`
229* Should follow GROUP BY clause
230
231```SQL
232SELECT column1, 
233SUM(column2)
234
235FROM "list-of-tables"
236
237GROUP BY "column-list"
238
239HAVING "condition";
240```
241
242```SQL
243SELECT dept, avg(salary)
244FROM employee
245GROUP BY dept
246HAVING avg(salary) > 20000;
247
248SELECT COUNT(customerid), state FROM customers GROUP BY state HAVING COUNT(customerid) >1;
249
250SELECT item, MAX(price), MIN(price) FROM items_ordered GROUP BY item HAVING MAX(price) > 190.00;
251
252SELECT COUNT(*), customerid, SUM(quantity) FROM items_ordered GROUP BY customerid HAVING SUM(quantity) > 1;
253```
254
255
256### ORDER BY
257
258* ASC by default
259* How does ordering by multiple columns work?
260
261```SQL
262SELECT employee_id, dept, name, age, salary
263FROM employee_info
264WHERE dept = 'Sales'
265ORDER BY salary, age DESC;
266
267
268SELECT lastname, firstname, city FROM customers ORDER BY lastname ASC;
269
270```
271### Combining Conditions & Boolean Operators
272
273* `AND` joins two or more conditions in the `WHERE` clause
274* `OR` either side must be true
275
276```SQL
277SELECT column1, 
278SUM(column2)
279FROM "list-of-tables"
280WHERE "condition1" AND 
281"condition2";
282
283SELECT employeeid, firstname, lastname, title, salary
284FROM employee_info
285WHERE salary >= 45000.00 AND title = 'Programmer';
286
287SELECT customerid, order_date, item
288FROM items_ordered
289WHERE (item <> 'Snow shoes') AND (item <> 'Ear muffs');
290
291SELECT item,price FROM items_ordered WHERE (item LIKE 'S%') OR (item LIKE 'P%') OR (item LIKE 'F%');
292```
293
294### IN & BETWEEN
295* IN conditional operator is a set membership test operator. Tests whether a value is in the list of values provided
296
297* Can also use `NOT IN`
298
299* `BETWEEN` checks if value is between the specified values
300
301* IN can be simulated using many ORs and BETWEN can be simulated using AND statement
302
303* Can use `NOT` with `BETWEEN, IN`
304
305```SQL
306
307SELECT col1, SUM(col2)
308FROM "list-of-tables"
309WHERE col3 IN 
310       (list-of-values);
311
312SELECT employeeid, lastname, salary
313FROM employee_info
314WHERE lastname IN ('Hernandez', 'Jones', 'Roberts', 'Ruiz');
315
316
317
318SELECT col1, SUM(col2)
319FROM "list-of-tables"
320WHERE col3 BETWEEN value1 
321AND value2;
322
323
324SELECT employeeid, age, lastname, salary
325FROM employee_info
326WHERE age BETWEEN 30 AND 40;
327
328SELECT order_date, item, price
329FROM items_ordered
330WHERE price BETWEEN 10.00 AND 80.00;
331
332SELECT firstname, city, state FROM customers WHERE state IN ('Arizona', 'Washington', 'Oklahoma', 'Colorado', 'Hawaii');
333```
334
335
336### Mathematical Functions
337
338Some functions
339```
340ABS(X)
341SIGN(X)
342MOD(x,y) : Modulo
343FLOOR(x)
344CEILING(X)
345POWER(X,Y)
346ROUND(X)
347ROUND(X,d)
348SQRT(X)
349
350```
351
352### TABLE JOINS
353
354* Join statement allows us to relational aspect of relational databased
355* Joins allow you to link data from two or more tables together into a single query
356
357> Data Normalization is a technique of database design that is used to get the tables in your database into at least the third normal form (3NF). Basically, this means that you want to eliminate the redundancy of non-key data when constructing your tables. Each table should only have columns that depend on the primary key.
358
359```SQL
360(Inner Join/ Equijoin)
361
362SELECT customer_info.firstname, customer_info.lastname, purchases.item
363
364FROM customer_info, purchases
365
366WHERE customer_info.customer_number = purchases.customer_number;
367
368// Version 2 of Inner Join
369//
370
371SELECT customer_info.firstname, customer_info.lastname, purchases.item
372
373FROM customer_info INNER JOIN purchases
374
375ON customer_info.customer_number = purchases.customer_number;
376
377SELECT customers.customerid, customers.firstname, customers.lastname, items_ordered.order_date, items_ordered.item, items_ordered.price FROM customers INNER JOIN items_ordered
378ON customers.customerid = items_ordered.customerid; 
379```