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
There are many interesting usage of sql function, like case when which is super powerful. Hope you can find something interesting or useful from the code below. It’s used in the real working environment to fill a business data request. with vvvos as ( select distinct map.ccc_o_group_id os from base.dddd_o_mapping map inner join base.dddd_ccc_o_groups groups … Read more
这是我工作遇到的问题，从别的组里要来了一堆SQL code，大概是四五年前在那工作的人写的，感觉是old style，之前从没见过，贴出来给大家。 Q: What’s the meaning of (+) in SQL queries (Oracle)? A: It’s Oracle’s synonym for OUTER JOIN. Example： SELECT * FROM a, b WHERE b.id(+) = a.id gives the same result as SELECT * FROM a LEFT OUTER JOIN b ON b.id = a.id Or we can … Read more
Let’s learn 3 functions from http://docs.oracle.com 1. nvl(expr1,expr2) nvl lets you replace null (returned as a blank) with a string in the results of a query. if expr1 is null, then nvl returns expr2. if expr1 is not null, then nvl returns expr1. 2. stats_mode STATS_MODE takes as its argument a set of values and … Read more
ALTER SESSION ENABLE PARALLEL QUERY; ALTER SESSION SET NLS_DATE_FORMAT=’YYYY-MM-DD HH24:MI:SS’; select table1.contact_id,table1.contact_date,table1.people_login ,count(contact_id) over (partition by people_login order by table1.creation_datetime asc range numtodsinterval(365, ‘day’) preceding) as total_contacts ,sum(case when contact_type_code=’p’ then 1 else 0 end) over (partition by people_login order by table1.creation_datetime asc range numtodsinterval(365, ‘day’) preceding) as phone_contacts From contacts table1 where table1.marketplace=232435465 This … Read more