高频题（0）
1. General SQL questions
| 1. How many new subscribers have we received every month starting Jan 2017?
2. How does that metric compare to the same month prior year'
3.What is the conversion rate of signups for Dec 2018?
4. There' s another table (Free Subs) with companyIDs that got a free subscription.How do we exclude them from our previous metrics？
Table:
+---------+---------+---------------+--------------+---+------+|
companyid I signupdate l subscriptiondate l terminationdate l year l month
+---------+---------+---------------+--------------+---+------+|
|13343128 | 2020-02-13 |2020-02-14 I NULL || 13343124 | 2020-02-13 I 2020-02-14 |NULL || 13343100 I 2020-02-13 I 2020-02-14 I NULL ll 13343090 | 2020-02-13 I NULL || 2020-02-13 || 13343074 I 2018-02-13 I 201 8-02-14I NULL ll 13342758 | 2018-03-23 I 2018-03-24 I NULL || 13342732 | 2018-04-05 | 2018-04-05 I NULL || 13342730 |2018-09-13 |NULL |I 2018-09-13 || 1342720 | 2017-01-05 |2017-01-06 I 2018-02- 13 || 11343128 |2017-02-08 |2017-02 -08 I 2018-08-13 || 11342610 |2017-03 -23 | 2017-04-23I 2018-11-21 || 11342720 | 2017-03-29 |2017-03-30
2. How does that metric compare to the same month prior year'
3.What is the conversion rate of signups for Dec 2018?
4. There' s another table (Free Subs) with companyIDs that got a free subscription.How do we exclude them from our previous metrics？
Table:
+---------+---------+---------------+--------------+---+------+|
companyid I signupdate l subscriptiondate l terminationdate l year l month
+---------+---------+---------------+--------------+---+------+|
|13343128 | 2020-02-13 |2020-02-14 I NULL || 13343124 | 2020-02-13 I 2020-02-14 |NULL || 13343100 I 2020-02-13 I 2020-02-14 I NULL ll 13343090 | 2020-02-13 I NULL || 2020-02-13 || 13343074 I 2018-02-13 I 201 8-02-14I NULL ll 13342758 | 2018-03-23 I 2018-03-24 I NULL || 13342732 | 2018-04-05 | 2018-04-05 I NULL || 13342730 |2018-09-13 |NULL |I 2018-09-13 || 1342720 | 2017-01-05 |2017-01-06 I 2018-02- 13 || 11343128 |2017-02-08 |2017-02 -08 I 2018-08-13 || 11342610 |2017-03 -23 | 2017-04-23I 2018-11-21 || 11342720 | 2017-03-29 |2017-03-30
2. Write a SQL Query
Write a SQL query that returns user ids who are both posters and bidders.
3. SQL vs NoSQL Choice Discussion
Discuss the choice between SQL and NoSQL databases in the context of the craft demo project you presented.
4. Domain Address Click Count
Given a long domain address and its click count, calculate the click counts for all subdomains. The counts for the same domain should be cumulative. For example, given the input '900, mail.google.com', the output should be: 'mail.google.com 900', 'google.com 900', 'com 900'.
5. CheckSum Follow-up
Given a 2D array, find a number (a) in each row that can be divided by another number (b) without a remainder, and return the total sum of a/b for each row. (The exact details of the follow-up question are not clear as the interviewee could not remember them precisely.)
