SQLize Online / PHPize Online  /  SQLtest Online

A A A
Share      Blog   Popular
Copy Format Clear
#write a SQL Query to select all customers who did their first order within the last 24 hours create table customer ( id int, created_at DATETIME DEFAULT CURRENT_TIMESTAMP, updated_at TIMESTAMP DEFAULT CURRENT_TIMESTAMP ON UPDATE CURRENT_TIMESTAMP, first_name varchar(64), last_name varchar(64) ); INSERT INTO customer VALUES ( 123, NOW(), now(), 'priya', 'Pandey' ), ( 134, NOW(), now(), 'pri12', 'Pandey' ), ( 193, NOW(), now(), 'pri56a', 'Pandey' ); create table orders ( id int, created_at DATETIME DEFAULT CURRENT_TIMESTAMP, updated_at TIMESTAMP DEFAULT CURRENT_TIMESTAMP ON UPDATE CURRENT_TIMESTAMP, status varchar(64), cust_id int, product_id int ); INSERT INTO orders VALUES ( 12453, now(), now(), 'deliver', 123, 2345 ), ( 13344, now(), now(), 'approve', 345, 3456 ), ( 13493, now(), now(), 'pending', 456, 4672 ); create table product ( id int, created_at DATETIME DEFAULT CURRENT_TIMESTAMP, updated_at TIMESTAMP DEFAULT CURRENT_TIMESTAMP ON UPDATE CURRENT_TIMESTAMP, category varchar(64), name varchar(64), percent float ); INSERT INTO product VALUES ( 1243, now(), now(), 'cloth', 'tshirt', 23 ), ( 1344, now(), now(), 'kids', 'frock', 23.44 ), ( 12493, now(), now(), 'kids', 'caps', 46.72 ); Select * from customer where exists( select id from orders where TIMESTAMPDIFF( HOUR, customer.created_at, orders.created_at ) <= 24 ); ##### select all customers who did their 3rd orders within 24 hours select c.id from customer c join orders o on c.id = o.cust_id where TIMESTAMPDIFF(HOUR, c.created_at, o.created_at) <= 24 GROUP BY c.id HAVING COUNT(DISTINCT o.id ) = 3;
Stuck with a problem? Got Error? Ask ChatGPT!
Copy Clear