SQLite 3.27

By using db<>fiddle, you agree to license everything you submit by Creative Commons CC0.

CREATE TABLE Tags (p_tags TEXT, p_urls LONGVARCHAR);
✓
INSERT INTO Tags VALUES
  ('foo', 'url1'), ('foo', 'url1'), ('foo', 'url1'), ('foo', 'url2'), ('foo', 'url3'), 
  ('bar', 'url1'), ('bar', 'url1'), ('bar', 'url2'), ('bar', 'url2'), ('bar', 'url2');
✓
WITH cte AS (
  SELECT p_urls, ROW_NUMBER() OVER (PARTITION BY p_urls) rn 
  FROM Tags WHERE p_tags = 'foo' 
  INTERSECT
  SELECT p_urls, ROW_NUMBER() OVER (PARTITION BY p_urls) rn 
  FROM Tags WHERE p_tags = 'bar'
)
SELECT p_urls FROM cte;


p_urls


url1

url1

url2


WITH cte AS (
  SELECT p_urls, 
         ROW_NUMBER() OVER (PARTITION BY p_tags, p_urls) rn 
  FROM Tags 
  WHERE p_tags IN ('foo', 'bar')
)
SELECT p_urls 
FROM cte
GROUP BY p_urls, rn
HAVING COUNT(*) > 1;


p_urls


url1

url1

url2