View difference between Paste ID: Y4p9EX6A and M72mNPcx

--DROP TABLE Replies; DROP TABLE emails;
+--DROP TABLE Replies; DROP TABLE emails;
 
 CREATE TABLE emails
 (
   id serial NOT NULL,
   subject text,
   created_at timestamp without time zone,
   folder text,
   message_id text,
   CONSTRAINT emails_pkey PRIMARY KEY (id)
 )
 WITH (
   OIDS=FALSE
 );
 
 -- Index: emails_message_id_index
 
 -- DROP INDEX emails_message_id_index;
 
 CREATE INDEX emails_message_id_index
   ON emails
   USING btree
   (message_id COLLATE pg_catalog."default");
 
 -- NOT IN THREAD
 
 INSERT INTO emails(
     subject, created_at, folder,  message_id)
     VALUES ('Not part of thread', (NOW() - INTERVAL '100 MINUTE'), 'INBOX', 'CAEBV8YRGwkNSb3cxquS5abSCmnwLn37GxCpg74mQe7=3SC5cdQ@mail.gmail.com');
 
 --THREAD 1 EMAILS
 
  INSERT INTO emails(
     subject, created_at, folder, message_id)
     VALUES ('howzitgoin', (NOW() - INTERVAL '90 MINUTE'), null, '53856b1448c89_23fa9605badd015951@3a139e8c-0b81-42c2-8e59-133c262e96a9.mail');
 
 INSERT INTO emails(
     subject, created_at, folder, message_id)
     VALUES ('Re: howzitgoin', (NOW() - INTERVAL '80 MINUTE'), 'INBOX', 'CAEBV8YTu_A6LtP_uGuQ-QSVj3zojWUiwcjGZpsPPEz1Pj3_i1A@mail.gmail.com');
 
  INSERT INTO emails(
     subject, created_at, folder, message_id)
     VALUES ('Re: howzitgoin', (NOW() - INTERVAL '70 MINUTE'), 'INBOX', '53856b88a2a09_23fa9605badd01601b@3a139e8c-0b81-42c2-8e59-133c262e96a9.mail');
 
 INSERT INTO emails(
     subject, created_at, folder, message_id)
     VALUES ('Re: howzitgoin', (NOW() - INTERVAL '60 MINUTE'), 'INBOX', 'CAEBV8YT6vx6buOKUga4f=bcNGq_=WzwiqEzm2FWm3HoLZ8SbJA@mail.gmail.com');
 
 INSERT INTO emails(
     subject, created_at, folder,  message_id)
     VALUES ('Re: howzitgoin', (NOW() - INTERVAL '50 MINUTE'), 'INBOX', 'CAEBV8YRGwkNSb3cxquS5abSCmnwLn37GxCpg74mQe7=3SC5cdQ@mail.gmail.com');
 
 -- THREAD 2 EMAILS
 
 INSERT INTO emails(
     subject, created_at, folder,  message_id)
     VALUES ('thready mercury', (NOW() - INTERVAL '40 MINUTE'), 'INBOX', '[email protected]');
 
  INSERT INTO emails(
     subject, created_at, folder,  message_id)
     VALUES ('RE: thready mercury', (NOW() - INTERVAL '30 MINUTE'), null, '5386c3c3f364d_23ff44def2de8849cf@55cedd07-f558-4cc4-b5d2-046dc7642b91.mail');
 
  INSERT INTO emails(
     subject, created_at, folder,  message_id)
     VALUES ('RE: thready mercury', (NOW() - INTERVAL '20 MINUTE'), 'INBOX', '[email protected]');
 
 -- Table: replies
 
 -- DROP TABLE replies;
 
 CREATE TABLE replies
 (
   id serial NOT NULL,
   message_id text,
   created_at timestamp without time zone,
   email_id integer NOT NULL,
   CONSTRAINT replies_pkey PRIMARY KEY (id),
   CONSTRAINT replies_email_id_fkey FOREIGN KEY (email_id)
       REFERENCES emails (id) MATCH SIMPLE
       ON UPDATE NO ACTION ON DELETE CASCADE
 )
 WITH (
   OIDS=FALSE
 );
 
 
 -- Index: replies_email_id_index
 
 -- DROP INDEX replies_email_id_index;
 
 CREATE INDEX replies_email_id_index
   ON replies
   USING btree
   (email_id);
 
 -- Index: replies_message_id_index
 
 -- DROP INDEX replies_message_id_index;
 
 CREATE INDEX replies_message_id_index
   ON replies
   USING btree
   (message_id COLLATE pg_catalog."default");
 
 -- THREAD 1 REPLIES
 
 INSERT INTO replies(message_id, created_at, email_id)
-            VALUES ('53856b1448c89_23fa9605badd015951@3a139e8c-0b81-42c2-8e59-133c262e96a9.mail', now(), 2);
+            VALUES ('53856b1448c89_23fa9605badd015951@3a139e8c-0b81-42c2-8e59-133c262e96a9.mail', (NOW() - INTERVAL '85 MINUTE'), 2);
 
 INSERT INTO replies(message_id, created_at, email_id)
-            VALUES ('53856b1448c89_23fa9605badd015951@3a139e8c-0b81-42c2-8e59-133c262e96a9.mail', now(), 3);
+            VALUES ('53856b1448c89_23fa9605badd015951@3a139e8c-0b81-42c2-8e59-133c262e96a9.mail', (NOW() - INTERVAL '75 MINUTE'), 3);
 
 INSERT INTO replies(message_id, created_at, email_id)
-            VALUES ('CAEBV8YTu_A6LtP_uGuQ-QSVj3zojWUiwcjGZpsPPEz1Pj3_i1A@mail.gmail.com', now(), 3);
+            VALUES ('CAEBV8YTu_A6LtP_uGuQ-QSVj3zojWUiwcjGZpsPPEz1Pj3_i1A@mail.gmail.com', (NOW() - INTERVAL '75 MINUTE'), 3);
 
 INSERT INTO replies(message_id, created_at, email_id)
-            VALUES ('53856b88a2a09_23fa9605badd01601b@3a139e8c-0b81-42c2-8e59-133c262e96a9.mail', now(), 4);
+            VALUES ('53856b88a2a09_23fa9605badd01601b@3a139e8c-0b81-42c2-8e59-133c262e96a9.mail', (NOW() - INTERVAL '65 MINUTE'), 4);
 
 INSERT INTO replies(message_id, created_at, email_id)
-            VALUES ('CAEBV8YTu_A6LtP_uGuQ-QSVj3zojWUiwcjGZpsPPEz1Pj3_i1A@mail.gmail.com', now(), 4);
+            VALUES ('CAEBV8YTu_A6LtP_uGuQ-QSVj3zojWUiwcjGZpsPPEz1Pj3_i1A@mail.gmail.com', (NOW() - INTERVAL '65 MINUTE'), 4);
 
 INSERT INTO replies(message_id, created_at, email_id)
-            VALUES ('53856b1448c89_23fa9605badd015951@3a139e8c-0b81-42c2-8e59-133c262e96a9.mail', now(), 4);
+            VALUES ('53856b1448c89_23fa9605badd015951@3a139e8c-0b81-42c2-8e59-133c262e96a9.mail', (NOW() - INTERVAL '65 MINUTE'), 4);
 
 INSERT INTO replies(message_id, created_at, email_id)
-            VALUES ('53856b88a2a09_23fa9605badd01601b@3a139e8c-0b81-42c2-8e59-133c262e96a9.mail', now(), 5);
+            VALUES ('53856b88a2a09_23fa9605badd01601b@3a139e8c-0b81-42c2-8e59-133c262e96a9.mail', (NOW() - INTERVAL '55 MINUTE'), 5);
 
 INSERT INTO replies(message_id, created_at, email_id)
-            VALUES ('CAEBV8YTu_A6LtP_uGuQ-QSVj3zojWUiwcjGZpsPPEz1Pj3_i1A@mail.gmail.com', now(), 5);
+            VALUES ('CAEBV8YTu_A6LtP_uGuQ-QSVj3zojWUiwcjGZpsPPEz1Pj3_i1A@mail.gmail.com', (NOW() - INTERVAL '55 MINUTE'), 5);
 
 INSERT INTO replies(message_id, created_at, email_id)
-            VALUES ('53856b1448c89_23fa9605badd015951@3a139e8c-0b81-42c2-8e59-133c262e96a9.mail', now(), 5);
+            VALUES ('53856b1448c89_23fa9605badd015951@3a139e8c-0b81-42c2-8e59-133c262e96a9.mail', (NOW() - INTERVAL '55 MINUTE'), 5);
 
 -- THREAD 2 REPLIES
 
 
 INSERT INTO replies(message_id, created_at, email_id)
-            VALUES ('[email protected]', now(), 8);
+            VALUES ('[email protected]', (NOW() - INTERVAL '45 MINUTE'), 8);
 
 INSERT INTO replies(message_id, created_at, email_id)
-            VALUES ('5386c3c3f364d_23ff44def2de8849cf@55cedd07-f558-4cc4-b5d2-046dc7642b91.mail', now(), 9);
+            VALUES ('5386c3c3f364d_23ff44def2de8849cf@55cedd07-f558-4cc4-b5d2-046dc7642b91.mail', (NOW() - INTERVAL '35 MINUTE'), 9);
 
 INSERT INTO replies(message_id, created_at, email_id)
-            VALUES ('[email protected]', now(), 9);
+            VALUES ('[email protected]', (NOW() - INTERVAL '35 MINUTE'), 9);
 
 --- my attempt which does not work
--- MY FAILED ATTEMPT
+SELECT  "emails".id, subject, "count", "emails".created_at
-SELECT  "emails".id, subject, "emails".created_at, "count"
+
 LEFT JOIN
 (
 	SELECT "email_id", count(*) AS "count" FROM "replies" GROUP BY "email_id"
 ) AS "r"
 ON ("emails"."id" = "r"."email_id")
 WHERE (("folder" = 'INBOX')
   --AND ("emails"."id" NOT IN (SELECT "email_id" FROM "replies"))
 ) ORDER BY "created_at" DESC LIMIT 7 OFFSET 0;
 
 
 --EXPECTED OUTPUT
 -----------------------------------------------------------
 -- ID | SUBJECT             | COUNT
 -- =========================================================
 -- 9  | RE: thready mercury | 3
--- 9  | RE: howzitgoin      | 4
+-- 6  | RE: howzitgoin      | 4
--- 9  | Not part of thread  | (null)
+-- 1  | Not part of thread  | (null)