drop table persons;
create table persons (
id int,
name varchar(100),
gender varchar(10),
location varchar(100));
insert into persons values (1, 'John', 'm', 'Los Angeles');
insert into persons values (2, 'Mike', 'm', 'Seattle');
insert into persons values (3, 'Ann', 'f', 'New York');
insert into persons values (4, 'Emily', 'f', 'Los Angeles');
-- Expected Output
/*
location | male_count | female_count
Los Angeles | 1 | 1
New York | 0 | 1
Seattle. | 1 | 0
*/
select location, count(gender) as male_count, count(gender) as female_count
from persons
where gender = 'm' and
group by location;
create table user_events
(id integer,
event_user varchar(20),
web_page varchar(20),
start_time timestamp
);
insert into user_events values (1, 'raj', 'home', to_timestamp('01-NOV-2020 14:01:00', 'dd-mon-yyyy hh24:mi:ss'));
insert into user_events values (1, 'raj', 'page 1', to_timestamp('01-NOV-2020 14:02:00', 'dd-mon-yyyy hh24:mi:ss'));
insert into user_events values (1, 'raj', 'page 3', to_timestamp('01-NOV-2020 14:05:00', 'dd-mon-yyyy hh24:mi:ss'));
insert into user_events values (2, 'kumar', 'page 1', to_timestamp('01-NOV-2020 14:10:00', 'dd-mon-yyyy hh24:mi:ss'));
insert into user_events values (2, 'kumar', 'page 2', to_timestamp('01-NOV-2020 14:20:00', 'dd-mon-yyyy hh24:mi:ss'));
insert into user_events values (2, 'kumar', 'page 3', to_timestamp('01-NOV-2020 14:40:00', 'dd-mon-yyyy hh24:mi:ss'));
select * from user_events;
Telegram: DB developers channel