Untitled

-- Create users table
CREATE TABLE users (
  userid INTEGER PRIMARY KEY,
  user_phone TEXT,
  user_email TEXT
);

-- Populate with some data
INSERT INTO users VALUES 
  (1, '555-1234', 'user1@email.com'),
  (2, '555-2345', 'user2@email.com'),
  (3, '555-3456', 'user3@email.com');

-- Create notifications table  
CREATE TABLE notifications (
  id INTEGER PRIMARY KEY, 
  userid INTEGER,
  notification_text TEXT,
  read BOOLEAN
);

-- Populate with some data
INSERT INTO notifications VALUES
  (1, 1, 'Notification 1', 0),
  (2, 1, 'Notification 2', 0),
  (3, 2, 'Notification 3', 1),
  (4, 3, 'Notification 4', 1),
  (5, 1, 'Notification 5', 0);

--Query---
SELECT 
  u.userid,
  u.user_email,
  COUNT(n.userid) AS unread_count 
FROM users u
LEFT JOIN notifications n 
  ON n.userid = u.userid 
 AND n.read = 0
GROUP BY u.userid, u.user_email