Run Code
|
API
|
Code Wall
|
Users
|
Misc
|
Feedback
|
Login
|
Theme
|
Privacy
|
Blog
Sql flight 1
CREATE TABLE flights_table(visitor CHAR(3), DATE INT) INSERT INTO flights_table VALUES ('A', 1); INSERT INTO flights_table VALUES ('B', 1); INSERT INTO flights_table VALUES ('C', 1); INSERT INTO flights_table VALUES ('C', 2); INSERT INTO flights_table VALUES ('D', 2); INSERT INTO flights_table VALUES ('D', 3); INSERT INTO flights_table VALUES ('E', 3); INSERT INTO flights_table VALUES ('A', 4); INSERT INTO flights_table VALUES ('E', 4); INSERT INTO flights_table VALUES ('F', 4); SELECT Date, COUNT(DISTINCT visitor) AS total_visitor, COUNT(DISTINCT visitor) - LAG(COUNT(DISTINCT visitor)) OVER (ORDER BY Date) AS visitor_change FROM flights_table GROUP BY Date ORDER BY Date; SELECT date, SUM(CASE WHEN date = first_date THEN 1 ELSE 0) AS total_new_visitors FROM( SELECT date, MIN(Date) OVER(PARTITION BY visitor) AS first_date FROM flights_table)t GROUP BY date ORDER BY date;
run
|
edit
|
history
|
help
0
Please
log in
to post a comment.
MC170402209
add-empty-rows-after-certain-records-in-a-table
NAMES
exercise 1 – fines – database import 1.0.1.sql
DETAILS
Test1
TUAN 7_QUAN LY DE TAI
MixT
Mywall
try catch, raiserror and throw
stackse - search stackoverflow differently
Please log in to post a comment.