海外华人红娘

We have 3 tables Orders table (ORDER_ID, CUSTOMER_ID, ORDER_DATE) Detail table (ORDER_ID, PRODUCT_ID, PRICE, UNITS) Customers table (CUSTOMER_ID, SIGNUP_DATE, COUNTRY) Data request: can you write a query to calculate % of customers that made an order each month, by signup month. Please return 3 columns: signup month, order month, % of customers Possible solution: with … Read more

data: code A1 A2 A11 A22 A111 A222 A3333 A4444   We want to select a data set as follows. A1 A2 A11 A22 A111 A222   Solution: SELECT code FROM data WHERE code LIKE ‘A[0-9]’  — 1 digit OR    code LIKE ‘A[1-9][0-9]’  — 2 digits OR    code LIKE ‘A[1-9][0-9][0-9]’  — 3 digits   We … Read more

Table name is dogs. The table includes columns 1. name varchar 2. breed varchar 3. weight float 4. color varchar 5. age int The dogs will enter an elevator one by one. We need to know what the running total weight is. The dogs must be ordered by name as well. Possible solution: It’s a … Read more

I found there are lots of resource for SQL interview questions. It can help you on refreshing your knowledge of sql. Go through the below list before you go to an interview which might test your sql skill. 1. 练习题库 题库一 https://leetcode.com/problemset/database/ 题库二 https://www.hackerrank.com/domains/sql If we would not use subquery, we can use temporary table. … Read more

When we get message similar as below, usually we did not indicate a table for that column. But  sometimes it means you have written the same thing twice in select statement and you need to check if you are selecting the same column twice in the same select statement. ‘Statement 1 is not valid. ERROR: … Read more

Add days, weeks, month, quarter, year to a date: use positive number #minus days, weeks, month, quarter, year from a date: use negative number Example # minus 1 week from January 18th, 2019 : processed_date >= dateadd(week, -1, ‘20190118’) #or processed_date >= dateadd(weeks, -1, ‘20190118’) #week and weeks are the same for dateadd function. #you … Read more