Pipe operator data and queries that can be used in https://sqlite.org/fiddle/index.html

sqlite-pipe.sql

-- Create the customer table
CREATE TABLE customer (
    c_custkey INTEGER PRIMARY KEY,
    c_name TEXT NOT NULL,
    c_address TEXT NOT NULL,
    c_phone TEXT NOT NULL
);

-- Create the orders table
CREATE TABLE orders (
    o_orderkey INTEGER PRIMARY KEY,
    o_custkey INTEGER,
    o_orderdate TEXT NOT NULL,
    o_comment TEXT NOT NULL,
    FOREIGN KEY (o_custkey) REFERENCES customer(c_custkey)
);

-- Insert sample data into the customer table
INSERT INTO customer (c_custkey, c_name, c_address, c_phone) VALUES (1, 'Customer A', '123 Elm St', '555-1234');
INSERT INTO customer (c_custkey, c_name, c_address, c_phone) VALUES (2, 'Customer B', '456 Oak St', '555-5678');
INSERT INTO customer (c_custkey, c_name, c_address, c_phone) VALUES (3, 'Customer C', '789 Maple St', '555-8765');
INSERT INTO customer (c_custkey, c_name, c_address, c_phone) VALUES (4, 'Customer D', '135 Pine St', '555-1357');
INSERT INTO customer (c_custkey, c_name, c_address, c_phone) VALUES (5, 'Customer E', '246 Birch St', '555-2468');

-- Insert sample data into the orders table
INSERT INTO orders (o_orderkey, o_custkey, o_orderdate, o_comment) VALUES (1, 1, '2024-01-01', 'Quick delivery');
INSERT INTO orders (o_orderkey, o_custkey, o_orderdate, o_comment) VALUES (2, 1, '2024-01-02', 'Regular order');
INSERT INTO orders (o_orderkey, o_custkey, o_orderdate, o_comment) VALUES (3, 2, '2024-01-03', 'Repeat customer');
INSERT INTO orders (o_orderkey, o_custkey, o_orderdate, o_comment) VALUES (4, 2, '2024-01-04', 'Large order');
INSERT INTO orders (o_orderkey, o_custkey, o_orderdate, o_comment) VALUES (5, 2, '2024-01-05', 'Special request');
INSERT INTO orders (o_orderkey, o_custkey, o_orderdate, o_comment) VALUES (6, 3, '2024-01-06', 'Quick delivery');
INSERT INTO orders (o_orderkey, o_custkey, o_orderdate, o_comment) VALUES (7, 4, '2024-01-07', 'Order for testing');
INSERT INTO orders (o_orderkey, o_custkey, o_orderdate, o_comment) VALUES (8, 4, '2024-01-08', 'unusual packages');
INSERT INTO orders (o_orderkey, o_custkey, o_orderdate, o_comment) VALUES (9, 5, '2024-01-09', 'Customer returned');
INSERT INTO orders (o_orderkey, o_custkey, o_orderdate, o_comment) VALUES (10, 5, '2024-01-10', 'Fast delivery');
INSERT INTO orders (o_orderkey, o_custkey, o_orderdate, o_comment) VALUES (11, 5, '2024-01-11', 'unusual packages');
INSERT INTO orders (o_orderkey, o_custkey, o_orderdate, o_comment) VALUES (12, 5, '2024-01-12', 'Frequent customer');

-- Standard SQL query
SELECT c_count, COUNT(*) AS custdist
FROM
  ( SELECT c_custkey, COUNT(o_orderkey) c_count
    FROM customer
    LEFT OUTER JOIN orders ON c_custkey = o_custkey
      AND o_comment NOT LIKE '%unusual%packages%'
    GROUP BY c_custkey
  ) AS c_orders
GROUP BY c_count
ORDER BY custdist DESC, c_count DESC;

-- Pipe operator equivalent query
FROM customer
|> LEFT OUTER JOIN orders ON c_custkey = o_custkey AND o_comment NOT LIKE '%unusual%packages%'
|> AGGREGATE COUNT(o_orderkey) c_count GROUP BY c_custkey
|> AGGREGATE COUNT(*) AS custdist GROUP BY c_count
|> ORDER BY custdist DESC, c_count DESC;