--- Query: 001 SANITY CHECK ---
SELECT 'Journals' AS Entity, Count(*) AS N FROM Journals
UNION ALL
SELECT 'Articles' AS Entity, Count(*) AS N FROM Articles
UNION ALL SELECT 'References' AS Entity, Count(*) AS N FROM References;


--- Query: 002 Articles per journal ---
TRANSFORM Count(A.ARTICLE_ID) AS Articles_Per_Year
SELECT A.JOURNAL_ID
FROM ARTICLES AS A
GROUP BY A.JOURNAL_ID
PIVOT A.P_YEAR In (2020,2021,2022,2023,2024);


--- Query: 003 Articles per year ---
SELECT A.P_YEAR, Count(A.ARTICLE_ID) AS Total_Articles, Round(
        100.0 * Count(A.ARTICLE_ID) /
        (SELECT Count(*) FROM ARTICLES),
        2
    ) AS Percentage_of_Total
FROM ARTICLES AS A
GROUP BY A.P_YEAR
ORDER BY A.P_YEAR;


--- Query: 004 Articles per quartile ---
SELECT J.QUART, Count(A.ARTICLE_ID) AS Total_Articles, Round(
        100.0 * Count(A.ARTICLE_ID) /
        (SELECT Count(*) FROM ARTICLES),
        2
    ) AS Percentage_of_Total
FROM JOURNALS AS J INNER JOIN ARTICLES AS A ON J.JOURNAL_ID = A.JOURNAL_ID
GROUP BY J.QUART
ORDER BY J.QUART;


--- Query: 005 Articles per Subject ---
SELECT IIf(J.SUBJECT="EDUCATION","Education",IIf(J.SUBJECT="LIBRARY","Library Science",IIf(J.SUBJECT="PHYSICS","Physics",IIf(J.SUBJECT="COMPUTER","Computer Science","Other")))) AS Subject_Label, Count(A.ARTICLE_ID) AS Total_Articles, Round(         100.0 * Count(A.ARTICLE_ID) /         (SELECT Count(*) FROM ARTICLES),         2     ) AS Percentage_of_Total
FROM JOURNALS AS J INNER JOIN ARTICLES AS A ON J.JOURNAL_ID = A.JOURNAL_ID
GROUP BY J.SUBJECT
ORDER BY J.SUBJECT;


--- Query: 006 Articles and SJR ---
SELECT J.JOURNAL_ID, J.JOURNAL_NAME, J.[SJR 2024], Nz(C.Articles_Per_Journal,0) AS Articles_Per_Journal
FROM JOURNALS AS J LEFT JOIN (SELECT JOURNAL_ID, Count(*) AS Articles_Per_Journal FROM ARTICLES GROUP BY JOURNAL_ID)  AS C ON J.JOURNAL_ID = C.JOURNAL_ID
ORDER BY J.JOURNAL_ID;


--- Query: 007 Total references per journal ---
SELECT J.JOURNAL_ID, J.JOURNAL_NAME, Nz(RC.Total_Refs,0) AS Total_Refs
FROM JOURNALS AS J LEFT JOIN (SELECT A.JOURNAL_ID, Count(R.REF_ID) AS Total_Refs FROM ARTICLES AS A LEFT JOIN [REFERENCES] AS R ON A.ARTICLE_ID = R.ARTICLE_ID GROUP BY A.JOURNAL_ID)  AS RC ON J.JOURNAL_ID = RC.JOURNAL_ID
ORDER BY J.JOURNAL_ID;


--- Query: 008 References per year ---
SELECT A.P_YEAR, COUNT(R.REF_ID) AS Total_References
FROM ARTICLES AS A INNER JOIN [REFERENCES] AS R ON A.ARTICLE_ID = R.ARTICLE_ID
GROUP BY A.P_YEAR
ORDER BY A.P_YEAR;


--- Query: 009 Total references and articles per year ---
SELECT Y.P_YEAR, Y.Total_Articles, Y.Total_References, ROUND(Y.Total_References/Y.Total_Articles,2) AS Refs_Per_Article
FROM (SELECT A.P_YEAR, COUNT(A.ARTICLE_ID) AS Total_Articles, (             SELECT COUNT(R.REF_ID)             FROM ARTICLES AS A2             INNER JOIN [REFERENCES] AS R                 ON A2.ARTICLE_ID = R.ARTICLE_ID             WHERE A2.P_YEAR = A.P_YEAR         ) AS Total_References FROM ARTICLES AS A GROUP BY A.P_YEAR)  AS Y
ORDER BY Y.P_YEAR;


--- Query: 010 Total refs per subject ---
SELECT J.SUBJECT, COUNT(R.REF_ID) AS Total_References
FROM (JOURNALS AS J INNER JOIN ARTICLES AS A ON J.JOURNAL_ID = A.JOURNAL_ID) INNER JOIN [REFERENCES] AS R ON A.ARTICLE_ID = R.ARTICLE_ID
GROUP BY J.SUBJECT
ORDER BY COUNT(R.REF_ID) DESC;


--- Query: 011 Refs per subject and article ---
SELECT S.Subject, S.Total_Articles, Nz(R.Total_References,0) AS Total_References, IIf(S.Total_Articles=0,Null,Round(CDbl(Nz(R.Total_References,0))/S.Total_Articles,2)) AS Refs_Per_Article
FROM (SELECT J.SUBJECT AS Subject, Count(A.ARTICLE_ID) AS Total_Articles FROM JOURNALS AS J INNER JOIN ARTICLES AS A ON J.JOURNAL_ID = A.JOURNAL_ID GROUP BY J.SUBJECT)  AS S LEFT JOIN (SELECT J.SUBJECT AS Subject, Count(R.REF_ID) AS Total_References FROM (JOURNALS AS J INNER JOIN ARTICLES AS A ON J.JOURNAL_ID = A.JOURNAL_ID) INNER JOIN [REFERENCES] AS R ON A.ARTICLE_ID = R.ARTICLE_ID GROUP BY J.SUBJECT)  AS R ON S.Subject = R.Subject
ORDER BY 4 DESC;


--- Query: 012 Total refs per quart ---
SELECT J.QUART, Count(R.REF_ID) AS Total_References
FROM (JOURNALS AS J INNER JOIN ARTICLES AS A ON J.JOURNAL_ID = A.JOURNAL_ID) INNER JOIN [REFERENCES] AS R ON A.ARTICLE_ID = R.ARTICLE_ID
GROUP BY J.QUART
ORDER BY J.QUART;


--- Query: 013 Total refs per quart and per article per quart ---
SELECT Q.QUART, Q.Total_Articles, Nz(R.Total_References,0) AS Total_References, IIf(Q.Total_Articles=0,Null,Round(CDbl(Nz(R.Total_References,0))/Q.Total_Articles,2)) AS Refs_Per_Article
FROM (SELECT J.QUART, Count(A.ARTICLE_ID) AS Total_Articles FROM JOURNALS AS J INNER JOIN ARTICLES AS A ON J.JOURNAL_ID = A.JOURNAL_ID GROUP BY J.QUART)  AS Q LEFT JOIN (SELECT J.QUART, Count(R.REF_ID) AS Total_References FROM (JOURNALS AS J INNER JOIN ARTICLES AS A ON J.JOURNAL_ID = A.JOURNAL_ID) INNER JOIN [REFERENCES] AS R ON A.ARTICLE_ID = R.ARTICLE_ID GROUP BY J.QUART)  AS R ON Q.QUART = R.QUART
ORDER BY Q.QUART;


--- Query: 014 Num and AB articles and references ---
SELECT S.Ref_Style, S.Total_Articles, Nz(RS.Total_References,0) AS Total_References
FROM (SELECT AStyle.Ref_Style, Count(*) AS Total_Articles FROM (SELECT                     A.ARTICLE_ID,                     IIf(                         A.JOURNAL_ID In ("JOUR_010","JOUR_022","JOUR_023","JOUR_024","JOUR_025","JOUR_026","JOUR_027","JOUR_028","JOUR_029","JOUR_030","JOUR_031","JOUR_032"),                         "Numerical",                         IIf(                             A.JOURNAL_ID In ("JOUR_001","JOUR_002","JOUR_003","JOUR_004","JOUR_006","JOUR_007","JOUR_008","JOUR_009","JOUR_011","JOUR_012","JOUR_014","JOUR_015","JOUR_016","JOUR_017","JOUR_018","JOUR_019","JOUR_020","JOUR_021"),                             "Alphabetical",                             IIf(                                 A.JOURNAL_ID="JOUR_005" And A.P_YEAR Between 2020 And 2023, "Numerical",                                 IIf(A.JOURNAL_ID="JOUR_005" And A.P_YEAR=2024, "Alphabetical",                                 IIf(A.JOURNAL_ID="JOUR_013" And A.P_YEAR=2020, "Numerical",                                 IIf(A.JOURNAL_ID="JOUR_013" And A.P_YEAR Between 2021 And 2024, "Alphabetical",                                 "Unknown"))))                         )                     ) AS Ref_Style                 FROM ARTICLES AS A             )  AS AStyle WHERE AStyle.Ref_Style In ("Alphabetical","Numerical") GROUP BY AStyle.Ref_Style)  AS S LEFT JOIN (SELECT AStyle2.Ref_Style, Count(R.REF_ID) AS Total_References FROM (SELECT                     A.ARTICLE_ID,                     IIf(                         A.JOURNAL_ID In ("JOUR_010","JOUR_022","JOUR_023","JOUR_024","JOUR_025","JOUR_026","JOUR_027","JOUR_028","JOUR_029","JOUR_030","JOUR_031","JOUR_032"),                         "Numerical",                         IIf(                             A.JOURNAL_ID In ("JOUR_001","JOUR_002","JOUR_003","JOUR_004","JOUR_006","JOUR_007","JOUR_008","JOUR_009","JOUR_011","JOUR_012","JOUR_014","JOUR_015","JOUR_016","JOUR_017","JOUR_018","JOUR_019","JOUR_020","JOUR_021"),                             "Alphabetical",                             IIf(                                 A.JOURNAL_ID="JOUR_005" And A.P_YEAR Between 2020 And 2023, "Numerical",                                 IIf(A.JOURNAL_ID="JOUR_005" And A.P_YEAR=2024, "Alphabetical",                                 IIf(A.JOURNAL_ID="JOUR_013" And A.P_YEAR=2020, "Numerical",                                 IIf(A.JOURNAL_ID="JOUR_013" And A.P_YEAR Between 2021 And 2024, "Alphabetical",                                 "Unknown"))))                         )                     ) AS Ref_Style                 FROM ARTICLES AS A             )  AS AStyle2 LEFT JOIN [REFERENCES] AS R ON AStyle2.ARTICLE_ID = R.ARTICLE_ID WHERE AStyle2.Ref_Style In ("Alphabetical","Numerical") GROUP BY AStyle2.Ref_Style)  AS RS ON S.Ref_Style = RS.Ref_Style
ORDER BY S.Ref_Style;


--- Query: 015 References per Article ---
SELECT A.ARTICLE_ID, FIRST(A.ARTICLE_TITLE) AS ARTICLE_TITLE, Count(R.REF_ID) AS Reference_Count
FROM ARTICLES AS A LEFT JOIN [REFERENCES] AS R ON A.ARTICLE_ID = R.ARTICLE_ID
GROUP BY A.ARTICLE_ID
ORDER BY A.ARTICLE_ID;


--- Query: 016 Article_Reference_Distribution ---
SELECT Ref_Bin, Count(*) AS Articles_In_Bin
FROM Q_Article_Ref_Bins
GROUP BY Ref_Bin
ORDER BY Ref_Bin;


--- Query: 017 Articles with 0 refs ---
SELECT A.ARTICLE_ID, FIRST(A.ARTICLE_TITLE) AS ARTICLE_TITLE, A.JOURNAL_ID, FIRST(J.JOURNAL_NAME) AS JOURNAL_NAME, A.P_YEAR
FROM (ARTICLES AS A INNER JOIN JOURNALS AS J ON A.JOURNAL_ID = J.JOURNAL_ID) LEFT JOIN [REFERENCES] AS R ON A.ARTICLE_ID = R.ARTICLE_ID
GROUP BY A.ARTICLE_ID, A.JOURNAL_ID, A.P_YEAR
HAVING Count(R.REF_ID) = 0
ORDER BY A.JOURNAL_ID, A.P_YEAR, A.ARTICLE_ID;


--- Query: 018 Mean refs per subject ---
SELECT J.SUBJECT, AVG(Nz(RC.RefCount,0)) AS Mean_Refs
FROM (JOURNALS AS J INNER JOIN ARTICLES AS A ON J.JOURNAL_ID = A.JOURNAL_ID) LEFT JOIN (SELECT ARTICLE_ID, COUNT(*) AS RefCount FROM [REFERENCES] GROUP BY ARTICLE_ID)  AS RC ON A.ARTICLE_ID = RC.ARTICLE_ID
GROUP BY J.SUBJECT;


--- Query: 018 Range per Style ---
TRANSFORM Count(*) AS Articles
SELECT B.Ref_Bin
FROM Q_Article_Ref_Bins AS B INNER JOIN [023 Article_Ref_Style] AS S ON B.ARTICLE_ID = S.ARTICLE_ID
GROUP BY B.Ref_Bin
PIVOT S.Ref_Style;


--- Query: 019 Mean references per year ---
SELECT A.P_YEAR, AVG(RC.RefCount) AS Mean_Refs
FROM ARTICLES AS A INNER JOIN (SELECT ARTICLE_ID, COUNT(*) AS RefCount FROM [REFERENCES] GROUP BY ARTICLE_ID)  AS RC ON A.ARTICLE_ID = RC.ARTICLE_ID
GROUP BY A.P_YEAR
ORDER BY A.P_YEAR;


--- Query: 020 references per subject AND year ---
SELECT J.SUBJECT, A.P_YEAR, AVG(Nz(RC.RefCount,0)) AS Mean_Refs
FROM (JOURNALS AS J INNER JOIN ARTICLES AS A ON J.JOURNAL_ID = A.JOURNAL_ID) LEFT JOIN (SELECT ARTICLE_ID, COUNT(*) AS RefCount FROM [REFERENCES] GROUP BY ARTICLE_ID)  AS RC ON A.ARTICLE_ID = RC.ARTICLE_ID
GROUP BY J.SUBJECT, A.P_YEAR
ORDER BY J.SUBJECT, A.P_YEAR;


--- Query: 022 YearXQuart Mean refs ---
TRANSFORM Avg(RefCount) AS MeanRefsPerArticle
SELECT P_YEAR
FROM Q_ArticleRefCounts
GROUP BY P_YEAR
PIVOT QUART IN ('Q1','Q2','Q3','Q4');


--- Query: 023 Article_Ref_Style ---
SELECT A.ARTICLE_ID, IIf(A.JOURNAL_ID In ("JOUR_010","JOUR_022","JOUR_023","JOUR_024","JOUR_025","JOUR_026","JOUR_027","JOUR_028","JOUR_029","JOUR_030","JOUR_031","JOUR_032"),"Numerical",IIf(A.JOURNAL_ID In ("JOUR_001","JOUR_002","JOUR_003","JOUR_004","JOUR_006","JOUR_007","JOUR_008","JOUR_009","JOUR_011","JOUR_012","JOUR_014","JOUR_015","JOUR_016","JOUR_017","JOUR_018","JOUR_019","JOUR_020","JOUR_021"),"Alphabetical",IIf(A.JOURNAL_ID="JOUR_005" And A.P_YEAR Between 2020 And 2023,"Numerical",IIf(A.JOURNAL_ID="JOUR_005" And A.P_YEAR=2024,"Alphabetical",IIf(A.JOURNAL_ID="JOUR_013" And A.P_YEAR=2020,"Numerical",IIf(A.JOURNAL_ID="JOUR_013" And A.P_YEAR Between 2021 And 2024,"Alphabetical","Unknown")))))) AS Ref_Style
FROM ARTICLES AS A;


--- Query: 024_URL_references ---
SELECT R.REF_ID, R.ARTICLE_ID, A.ARTICLE_TITLE, A.P_YEAR, J.JOURNAL_ID, J.JOURNAL_NAME, J.SUBJECT, J.QUART, R.REF_INDEX, R.REFERENCE_TEXT
FROM (JOURNALS AS J INNER JOIN ARTICLES AS A ON J.JOURNAL_ID = A.JOURNAL_ID) INNER JOIN [REFERENCES] AS R ON A.ARTICLE_ID = R.ARTICLE_ID
WHERE R.REFERENCE_TEXT Like "*http://*"
    OR R.REFERENCE_TEXT Like "*https://*"
    OR R.REFERENCE_TEXT Like "*www.*"
    OR R.REFERENCE_TEXT Like "*doi:*"
    OR R.REFERENCE_TEXT Like "*doi.org/*"
    OR R.REFERENCE_TEXT Like "*dx.doi.org/*"
ORDER BY J.JOURNAL_ID, A.P_YEAR, R.ARTICLE_ID, R.REF_INDEX;


--- Query: 024a http URL references ---
SELECT R.REF_ID, R.ARTICLE_ID, A.ARTICLE_TITLE, A.P_YEAR, J.JOURNAL_ID, J.JOURNAL_NAME, R.REF_INDEX, R.REFERENCE_TEXT
FROM (JOURNALS AS J INNER JOIN ARTICLES AS A ON J.JOURNAL_ID = A.JOURNAL_ID) INNER JOIN [REFERENCES] AS R ON A.ARTICLE_ID = R.ARTICLE_ID
WHERE R.REFERENCE_TEXT Like "*http://*"
    OR R.REFERENCE_TEXT Like "*https://*"
    OR R.REFERENCE_TEXT Like "*www.*"
ORDER BY J.JOURNAL_ID, A.P_YEAR, R.ARTICLE_ID, R.REF_INDEX;


--- Query: 024b DOI as URL references ---
SELECT R.REF_ID, R.ARTICLE_ID, A.ARTICLE_TITLE, A.P_YEAR, J.JOURNAL_ID, J.JOURNAL_NAME, R.REF_INDEX, R.REFERENCE_TEXT
FROM (JOURNALS AS J INNER JOIN ARTICLES AS A ON J.JOURNAL_ID = A.JOURNAL_ID) INNER JOIN [REFERENCES] AS R ON A.ARTICLE_ID = R.ARTICLE_ID
WHERE R.REFERENCE_TEXT Like "*doi.org/*"
    OR R.REFERENCE_TEXT Like "*dx.doi.org/*"
ORDER BY J.JOURNAL_ID, A.P_YEAR, R.ARTICLE_ID, R.REF_INDEX;


--- Query: 024bb_DOI_as_URL_ALL ---
SELECT R.REF_ID, R.ARTICLE_ID, R.REFERENCE_TEXT
FROM [REFERENCES] AS R
WHERE (
    R.REFERENCE_TEXT Like "*http://*"
    OR R.REFERENCE_TEXT Like "*https://*"
    OR R.REFERENCE_TEXT Like "*www.*"
)
AND
(
    R.REFERENCE_TEXT Like "*doi*org*"
);


--- Query: 024c DOI as strings URL references ---
SELECT R.REF_ID, R.ARTICLE_ID, A.ARTICLE_TITLE, A.P_YEAR, J.JOURNAL_ID, J.JOURNAL_NAME, R.REF_INDEX, R.REFERENCE_TEXT
FROM (JOURNALS AS J INNER JOIN ARTICLES AS A ON J.JOURNAL_ID = A.JOURNAL_ID) INNER JOIN [REFERENCES] AS R ON A.ARTICLE_ID = R.ARTICLE_ID
WHERE R.REFERENCE_TEXT Like "*doi:*"
    AND R.REFERENCE_TEXT Not Like "*doi.org/*"
    AND R.REFERENCE_TEXT Not Like "*dx.doi.org/*"
ORDER BY J.JOURNAL_ID, A.P_YEAR, R.ARTICLE_ID, R.REF_INDEX;


--- Query: 024cc_DOIs_as_strings ---
SELECT R.REF_ID, R.ARTICLE_ID, R.REFERENCE_TEXT
FROM [REFERENCES] AS R
WHERE (
    R.REFERENCE_TEXT Like "*doi:*"
    OR R.REFERENCE_TEXT Like "*doi :*"
)
AND NOT
(
    R.REFERENCE_TEXT Like "*http://*"
    OR R.REFERENCE_TEXT Like "*https://*"
    OR R.REFERENCE_TEXT Like "*www.*"
);


--- Query: 024d_URL_only_no_DOI ---
SELECT R.REF_ID, R.REF_INDEX, R.REFERENCE_TEXT, R.ARTICLE_ID
FROM [REFERENCES] AS R
WHERE (
    R.REFERENCE_TEXT Like "*http://*"
    OR R.REFERENCE_TEXT Like "*https://*"
    OR R.REFERENCE_TEXT Like "*www.*"
)
AND
R.REFERENCE_TEXT Not Like "*doi.org/*"
AND
R.REFERENCE_TEXT Not Like "*dx.doi.org/*"
AND
R.REFERENCE_TEXT Not Like "*doi:*";


--- Query: 024e_DOI_references_all_forms ---
SELECT R.REF_ID, R.REF_INDEX, R.REFERENCE_TEXT, R.ARTICLE_ID
FROM [REFERENCES] AS R
WHERE R.REFERENCE_TEXT Like "*doi.org/*"
OR R.REFERENCE_TEXT Like "*dx.doi.org/*"
OR R.REFERENCE_TEXT Like "*doi:*";


--- Query: 025 http and DOI overlap ---
SELECT Count(*) AS DOI_HTTP_overlap
FROM [REFERENCES]
WHERE (
    REFERENCE_TEXT Like "*http://*"
 OR REFERENCE_TEXT Like "*https://*"
 OR REFERENCE_TEXT Like "*www.*"
)
AND
REFERENCE_TEXT Like "*doi:*";


--- Query: 026_URLs_per_Journal ---
SELECT J.JOURNAL_ID, J.JOURNAL_NAME, Nz(T.Total_References,0) AS Total_References, Nz(U.URL_References,0) AS URL_References, Round((Nz(U.URL_References,0)/Nz(T.Total_References,1))*100,2) AS Percent_URL
FROM (JOURNALS AS J LEFT JOIN (SELECT A.JOURNAL_ID, Count(R.REF_ID) AS Total_References FROM ARTICLES AS A INNER JOIN [REFERENCES] AS R ON A.ARTICLE_ID = R.ARTICLE_ID GROUP BY A.JOURNAL_ID)  AS T ON J.JOURNAL_ID = T.JOURNAL_ID) LEFT JOIN (SELECT A.JOURNAL_ID, Count(R.REF_ID) AS URL_References FROM ARTICLES AS A INNER JOIN [REFERENCES] AS R ON A.ARTICLE_ID = R.ARTICLE_ID WHERE R.REFERENCE_TEXT Like "*http://*"             OR R.REFERENCE_TEXT Like "*https://*"             OR R.REFERENCE_TEXT Like "*www.*"             OR R.REFERENCE_TEXT Like "*doi.org/*"             OR R.REFERENCE_TEXT Like "*dx.doi.org/*"             OR R.REFERENCE_TEXT Like "*doi:*" GROUP BY A.JOURNAL_ID)  AS U ON J.JOURNAL_ID = U.JOURNAL_ID
ORDER BY J.JOURNAL_ID;


--- Query: 026a_Direct_URLs_per_Journal ---
SELECT J.JOURNAL_ID, J.JOURNAL_NAME, Nz(T.Total_References,0) AS Total_References, Nz(U.Direct_Web_Links,0) AS Direct_Web_Links, Round((Nz(U.Direct_Web_Links,0)/Nz(T.Total_References,1))*100,2) AS Percent_Direct_Web_Links
FROM (JOURNALS AS J LEFT JOIN (SELECT A.JOURNAL_ID, Count(R.REF_ID) AS Total_References FROM ARTICLES AS A INNER JOIN [REFERENCES] AS R ON A.ARTICLE_ID = R.ARTICLE_ID GROUP BY A.JOURNAL_ID)  AS T ON J.JOURNAL_ID = T.JOURNAL_ID) LEFT JOIN (SELECT A.JOURNAL_ID, Count(R.REF_ID) AS Direct_Web_Links FROM ARTICLES AS A INNER JOIN [REFERENCES] AS R ON A.ARTICLE_ID = R.ARTICLE_ID WHERE (                 R.REFERENCE_TEXT Like "*http://*"                 OR R.REFERENCE_TEXT Like "*https://*"                 OR R.REFERENCE_TEXT Like "*www.*"             )             AND R.REFERENCE_TEXT Not Like "*doi.org/*"             AND R.REFERENCE_TEXT Not Like "*dx.doi.org/*"             AND R.REFERENCE_TEXT Not Like "*doi:*" GROUP BY A.JOURNAL_ID)  AS U ON J.JOURNAL_ID = U.JOURNAL_ID
ORDER BY J.JOURNAL_ID;


--- Query: 026b_DOIs_per_Journal ---
SELECT J.JOURNAL_ID, J.JOURNAL_NAME, Nz(T.Total_References,0) AS Total_References, Nz(U.DOI_References,0) AS DOI_References, Round((Nz(U.DOI_References,0)/Nz(T.Total_References,1))*100,2) AS Percent_DOI
FROM (JOURNALS AS J LEFT JOIN (SELECT A.JOURNAL_ID, Count(R.REF_ID) AS Total_References FROM ARTICLES AS A INNER JOIN [REFERENCES] AS R ON A.ARTICLE_ID = R.ARTICLE_ID GROUP BY A.JOURNAL_ID)  AS T ON J.JOURNAL_ID = T.JOURNAL_ID) LEFT JOIN (SELECT A.JOURNAL_ID, Count(R.REF_ID) AS DOI_References FROM ARTICLES AS A INNER JOIN [REFERENCES] AS R ON A.ARTICLE_ID = R.ARTICLE_ID WHERE R.REFERENCE_TEXT Like "*doi.org/*"             OR R.REFERENCE_TEXT Like "*dx.doi.org/*"             OR R.REFERENCE_TEXT Like "*doi:*" GROUP BY A.JOURNAL_ID)  AS U ON J.JOURNAL_ID = U.JOURNAL_ID
ORDER BY J.JOURNAL_ID;


--- Query: 027_URLs_per_Subject ---
SELECT Q.SUBJECT, Q.Total_References, Q.URL_References, Round((Q.URL_References/Q.Total_References)*100,2) AS Percent_URL
FROM (SELECT J.SUBJECT, Count(R.REF_ID) AS Total_References, Sum(IIf(R.REFERENCE_TEXT Like "*http://*" Or R.REFERENCE_TEXT Like "*https://*" Or R.REFERENCE_TEXT Like "*www.*" Or R.REFERENCE_TEXT Like "*doi.org/*" Or R.REFERENCE_TEXT Like "*dx.doi.org/*" Or R.REFERENCE_TEXT Like "*doi:*",1,0)) AS URL_References FROM (JOURNALS AS J INNER JOIN ARTICLES AS A ON J.JOURNAL_ID = A.JOURNAL_ID) INNER JOIN [REFERENCES] AS R ON A.ARTICLE_ID = R.ARTICLE_ID GROUP BY J.SUBJECT)  AS Q
ORDER BY Round((Q.URL_References/Q.Total_References)*100,2) DESC;


--- Query: 027a_Direct_URLs_per_Subject ---
SELECT Q.SUBJECT, Q.Total_References, Q.Direct_Web_Links, Round((Q.Direct_Web_Links/Q.Total_References)*100,2) AS Percent_Direct_Web_Links
FROM (SELECT J.SUBJECT, Count(R.REF_ID) AS Total_References, Sum(IIf((R.REFERENCE_TEXT Like "*http://*" Or R.REFERENCE_TEXT Like "*https://*" Or R.REFERENCE_TEXT Like "*www.*") And R.REFERENCE_TEXT Not Like "*doi.org/*" And R.REFERENCE_TEXT Not Like "*dx.doi.org/*" And R.REFERENCE_TEXT Not Like "*doi:*",1,0)) AS Direct_Web_Links FROM (JOURNALS AS J INNER JOIN ARTICLES AS A ON J.JOURNAL_ID = A.JOURNAL_ID) INNER JOIN [REFERENCES] AS R ON A.ARTICLE_ID = R.ARTICLE_ID GROUP BY J.SUBJECT)  AS Q
ORDER BY Q.Direct_Web_Links / Q.Total_References DESC;


--- Query: 027b_DOIs_per_Subject ---
SELECT Q.SUBJECT, Q.Total_References, Q.DOI_References, Round((Q.DOI_References/Q.Total_References)*100,2) AS Percent_DOI
FROM (SELECT J.SUBJECT, Count(R.REF_ID) AS Total_References, Sum(IIf(R.REFERENCE_TEXT Like "*doi.org/*" Or R.REFERENCE_TEXT Like "*dx.doi.org/*" Or R.REFERENCE_TEXT Like "*doi:*",1,0)) AS DOI_References FROM (JOURNALS AS J INNER JOIN ARTICLES AS A ON J.JOURNAL_ID = A.JOURNAL_ID) INNER JOIN [REFERENCES] AS R ON A.ARTICLE_ID = R.ARTICLE_ID GROUP BY J.SUBJECT)  AS Q
ORDER BY Q.DOI_References / Q.Total_References DESC;


--- Query: 028_URL_refs_per_article_by_subject ---
SELECT Q.SUBJECT, Count(Q.ARTICLE_ID) AS Articles, Sum(Q.URL_Refs) AS Total_URL_Refs, Round(Avg(Q.URL_Refs),2) AS Mean_URL_Refs_Per_Article
FROM (SELECT J.SUBJECT, A.ARTICLE_ID, Nz(Sum(IIf(R.REFERENCE_TEXT Like "*http://*" Or R.REFERENCE_TEXT Like "*https://*" Or R.REFERENCE_TEXT Like "*www.*" Or R.REFERENCE_TEXT Like "*doi.org/*" Or R.REFERENCE_TEXT Like "*dx.doi.org/*" Or R.REFERENCE_TEXT Like "*doi:*",1,0)),0) AS URL_Refs FROM (JOURNALS AS J INNER JOIN ARTICLES AS A ON J.JOURNAL_ID = A.JOURNAL_ID) LEFT JOIN [REFERENCES] AS R ON A.ARTICLE_ID = R.ARTICLE_ID GROUP BY J.SUBJECT, A.ARTICLE_ID)  AS Q
GROUP BY Q.SUBJECT
ORDER BY Avg(Q.URL_Refs) DESC;


--- Query: 028_URL_refs_per_article_by_subject_no_0 ---
SELECT Q.SUBJECT, Count(Q.ARTICLE_ID) AS Articles, Sum(Q.URL_Refs) AS Total_URL_Refs, Round(Avg(Q.URL_Refs),2) AS Mean_URL_Refs_Per_Article
FROM (SELECT J.SUBJECT, A.ARTICLE_ID, Sum(IIf(R.REFERENCE_TEXT Like "*http://*" Or R.REFERENCE_TEXT Like "*https://*" Or R.REFERENCE_TEXT Like "*www.*" Or R.REFERENCE_TEXT Like "*doi.org/*" Or R.REFERENCE_TEXT Like "*dx.doi.org/*" Or R.REFERENCE_TEXT Like "*doi:*",1,0)) AS URL_Refs FROM (JOURNALS AS J INNER JOIN ARTICLES AS A ON J.JOURNAL_ID = A.JOURNAL_ID) INNER JOIN [REFERENCES] AS R ON A.ARTICLE_ID = R.ARTICLE_ID GROUP BY J.SUBJECT, A.ARTICLE_ID)  AS Q
GROUP BY Q.SUBJECT
ORDER BY Avg(Q.URL_Refs) DESC;


--- Query: 028a_Direct_Web_refs_per_article_by_subject ---
SELECT Q.SUBJECT, Count(Q.ARTICLE_ID) AS Articles, Sum(Q.Direct_Web_Links) AS Total_Direct_Web_Links, Round(Avg(Q.Direct_Web_Links),2) AS Mean_Direct_Web_Links_Per_Article
FROM (SELECT J.SUBJECT, A.ARTICLE_ID, Nz(Sum(IIf((R.REFERENCE_TEXT Like "*http://*" Or R.REFERENCE_TEXT Like "*https://*" Or R.REFERENCE_TEXT Like "*www.*") And R.REFERENCE_TEXT Not Like "*doi.org/*" And R.REFERENCE_TEXT Not Like "*dx.doi.org/*" And R.REFERENCE_TEXT Not Like "*doi:*",1,0)),0) AS Direct_Web_Links FROM (JOURNALS AS J INNER JOIN ARTICLES AS A ON J.JOURNAL_ID = A.JOURNAL_ID) LEFT JOIN [REFERENCES] AS R ON A.ARTICLE_ID = R.ARTICLE_ID GROUP BY J.SUBJECT, A.ARTICLE_ID)  AS Q
GROUP BY Q.SUBJECT
ORDER BY Avg(Q.Direct_Web_Links) DESC;


--- Query: 028a_Direct_Web_refs_per_article_by_subject_no_0 ---
SELECT Q.SUBJECT, Count(Q.ARTICLE_ID) AS Articles, Sum(Q.Direct_Web_Links) AS Total_Direct_Web_Links, Round(Avg(Q.Direct_Web_Links),2) AS Mean_Direct_Web_Links_Per_Article
FROM (SELECT J.SUBJECT, A.ARTICLE_ID, Sum(IIf((R.REFERENCE_TEXT Like "*http://*" Or R.REFERENCE_TEXT Like "*https://*" Or R.REFERENCE_TEXT Like "*www.*") And R.REFERENCE_TEXT Not Like "*doi.org/*" And R.REFERENCE_TEXT Not Like "*dx.doi.org/*" And R.REFERENCE_TEXT Not Like "*doi:*",1,0)) AS Direct_Web_Links FROM (JOURNALS AS J INNER JOIN ARTICLES AS A ON J.JOURNAL_ID = A.JOURNAL_ID) INNER JOIN [REFERENCES] AS R ON A.ARTICLE_ID = R.ARTICLE_ID GROUP BY J.SUBJECT, A.ARTICLE_ID)  AS Q
GROUP BY Q.SUBJECT
ORDER BY Avg(Q.Direct_Web_Links) DESC;


--- Query: 028b_DOIs_per_article_by_subject ---
SELECT Q.SUBJECT, Count(Q.ARTICLE_ID) AS Articles, Sum(Q.DOI_Refs) AS Total_DOI_Refs, Round(Avg(Q.DOI_Refs),2) AS Mean_DOI_Refs_Per_Article
FROM (SELECT J.SUBJECT, A.ARTICLE_ID, Nz(Sum(IIf(R.REFERENCE_TEXT Like "*doi.org/*" Or R.REFERENCE_TEXT Like "*dx.doi.org/*" Or R.REFERENCE_TEXT Like "*doi:*",1,0)),0) AS DOI_Refs FROM (JOURNALS AS J INNER JOIN ARTICLES AS A ON J.JOURNAL_ID = A.JOURNAL_ID) LEFT JOIN [REFERENCES] AS R ON A.ARTICLE_ID = R.ARTICLE_ID GROUP BY J.SUBJECT, A.ARTICLE_ID)  AS Q
GROUP BY Q.SUBJECT
ORDER BY Avg(Q.DOI_Refs) DESC;


--- Query: 028b_DOIs_per_article_by_subject_no_0 ---
SELECT Q.SUBJECT, Count(Q.ARTICLE_ID) AS Articles, Sum(Q.DOI_Refs) AS Total_DOI_Refs, Round(Avg(Q.DOI_Refs),2) AS Mean_DOI_Refs_Per_Article
FROM (SELECT J.SUBJECT, A.ARTICLE_ID, Sum(IIf(R.REFERENCE_TEXT Like "*doi.org/*" Or R.REFERENCE_TEXT Like "*dx.doi.org/*" Or R.REFERENCE_TEXT Like "*doi:*",1,0)) AS DOI_Refs FROM (JOURNALS AS J INNER JOIN ARTICLES AS A ON J.JOURNAL_ID = A.JOURNAL_ID) INNER JOIN [REFERENCES] AS R ON A.ARTICLE_ID = R.ARTICLE_ID GROUP BY J.SUBJECT, A.ARTICLE_ID)  AS Q
GROUP BY Q.SUBJECT
ORDER BY Avg(Q.DOI_Refs) DESC;


--- Query: 029_URLs_by_quart ---
SELECT J.QUART, Count(R.REF_ID) AS Total_References, Sum(IIf(R.REFERENCE_TEXT Like "*http://*" Or R.REFERENCE_TEXT Like "*https://*" Or R.REFERENCE_TEXT Like "*www.*" Or R.REFERENCE_TEXT Like "*doi.org/*" Or R.REFERENCE_TEXT Like "*dx.doi.org/*" Or R.REFERENCE_TEXT Like "*doi:*",1,0)) AS URL_References
FROM (JOURNALS AS J INNER JOIN ARTICLES AS A ON J.JOURNAL_ID = A.JOURNAL_ID) INNER JOIN [REFERENCES] AS R ON A.ARTICLE_ID = R.ARTICLE_ID
GROUP BY J.QUART
ORDER BY 3 DESC;


--- Query: 029a_Direct_by_quart ---
SELECT J.QUART, Count(R.REF_ID) AS Total_References, Sum(IIf((R.REFERENCE_TEXT Like "*http://*" Or R.REFERENCE_TEXT Like "*https://*" Or R.REFERENCE_TEXT Like "*www.*") And R.REFERENCE_TEXT Not Like "*doi.org/*" And R.REFERENCE_TEXT Not Like "*dx.doi.org/*" And R.REFERENCE_TEXT Not Like "*doi:*",1,0)) AS Direct_Web_Links
FROM (JOURNALS AS J INNER JOIN ARTICLES AS A ON J.JOURNAL_ID = A.JOURNAL_ID) INNER JOIN [REFERENCES] AS R ON A.ARTICLE_ID = R.ARTICLE_ID
GROUP BY J.QUART
ORDER BY 3 DESC;


--- Query: 029b_DOIs_by_quart ---
SELECT J.QUART, Count(R.REF_ID) AS Total_References, Sum(IIf(R.REFERENCE_TEXT Like "*doi.org/*" Or R.REFERENCE_TEXT Like "*dx.doi.org/*" Or R.REFERENCE_TEXT Like "*doi:*",1,0)) AS DOI_References
FROM (JOURNALS AS J INNER JOIN ARTICLES AS A ON J.JOURNAL_ID = A.JOURNAL_ID) INNER JOIN [REFERENCES] AS R ON A.ARTICLE_ID = R.ARTICLE_ID
GROUP BY J.QUART
ORDER BY 3 DESC;


--- Query: 029c_All_URL_by_quart ---
SELECT J.QUART, Count(R.REF_ID) AS Total_References, Sum(IIf(R.REFERENCE_TEXT Like "*http://*" Or R.REFERENCE_TEXT Like "*https://*" Or R.REFERENCE_TEXT Like "*www.*" Or R.REFERENCE_TEXT Like "*doi.org/*" Or R.REFERENCE_TEXT Like "*dx.doi.org/*" Or R.REFERENCE_TEXT Like "*doi:*",1,0)) AS URL_References, Round(Sum(IIf(R.REFERENCE_TEXT Like "*http://*" Or R.REFERENCE_TEXT Like "*https://*" Or R.REFERENCE_TEXT Like "*www.*" Or R.REFERENCE_TEXT Like "*doi.org/*" Or R.REFERENCE_TEXT Like "*dx.doi.org/*" Or R.REFERENCE_TEXT Like "*doi:*",1,0))/Count(R.REF_ID)*100,2) AS Percent_URL, Sum(IIf((R.REFERENCE_TEXT Like "*http://*" Or R.REFERENCE_TEXT Like "*https://*" Or R.REFERENCE_TEXT Like "*www.*") And R.REFERENCE_TEXT Not Like "*doi.org/*" And R.REFERENCE_TEXT Not Like "*dx.doi.org/*" And R.REFERENCE_TEXT Not Like "*doi:*",1,0)) AS Direct_Web_Links, Round(Sum(IIf((R.REFERENCE_TEXT Like "*http://*" Or R.REFERENCE_TEXT Like "*https://*" Or R.REFERENCE_TEXT Like "*www.*") And R.REFERENCE_TEXT Not Like "*doi.org/*" And R.REFERENCE_TEXT Not Like "*dx.doi.org/*" And R.REFERENCE_TEXT Not Like "*doi:*",1,0))/Count(R.REF_ID)*100,2) AS Percent_Direct_Web_Links, Sum(IIf(R.REFERENCE_TEXT Like "*doi.org/*" Or R.REFERENCE_TEXT Like "*dx.doi.org/*" Or R.REFERENCE_TEXT Like "*doi:*",1,0)) AS DOI_References, Round(Sum(IIf(R.REFERENCE_TEXT Like "*doi.org/*" Or R.REFERENCE_TEXT Like "*dx.doi.org/*" Or R.REFERENCE_TEXT Like "*doi:*",1,0))/Count(R.REF_ID)*100,2) AS Percent_DOI
FROM (JOURNALS AS J INNER JOIN ARTICLES AS A ON J.JOURNAL_ID = A.JOURNAL_ID) INNER JOIN [REFERENCES] AS R ON A.ARTICLE_ID = R.ARTICLE_ID
GROUP BY J.QUART
ORDER BY 4 DESC;


--- Query: 030_URL_per_article_by_subject ---
SELECT Q.QUART, Count(Q.ARTICLE_ID) AS Articles, Sum(Q.URL_Refs) AS Total_URL_Refs, Round(Avg(Q.URL_Refs),2) AS Mean_URL_Refs_Per_Article
FROM (SELECT J.QUART, A.ARTICLE_ID, Nz(Sum(IIf(R.REFERENCE_TEXT Like "*http://*" Or R.REFERENCE_TEXT Like "*https://*" Or R.REFERENCE_TEXT Like "*www.*" Or R.REFERENCE_TEXT Like "*doi.org/*" Or R.REFERENCE_TEXT Like "*dx.doi.org/*" Or R.REFERENCE_TEXT Like "*doi:*",1,0)),0) AS URL_Refs FROM (JOURNALS AS J INNER JOIN ARTICLES AS A ON J.JOURNAL_ID = A.JOURNAL_ID) LEFT JOIN [REFERENCES] AS R ON A.ARTICLE_ID = R.ARTICLE_ID GROUP BY J.QUART, A.ARTICLE_ID)  AS Q
GROUP BY Q.QUART
ORDER BY Avg(Q.URL_Refs) DESC;


--- Query: 030a_Direct_per_article_by_subject ---
SELECT Q.QUART, Count(Q.ARTICLE_ID) AS Articles, Sum(Q.Direct_Web_Links) AS Total_Direct_Web_Links, Round(Avg(Q.Direct_Web_Links),2) AS Mean_Direct_Web_Links_Per_Article
FROM (SELECT J.QUART, A.ARTICLE_ID, Nz(Sum(IIf((R.REFERENCE_TEXT Like "*http://*" Or R.REFERENCE_TEXT Like "*https://*" Or R.REFERENCE_TEXT Like "*www.*") And R.REFERENCE_TEXT Not Like "*doi.org/*" And R.REFERENCE_TEXT Not Like "*dx.doi.org/*" And R.REFERENCE_TEXT Not Like "*doi:*",1,0)),0) AS Direct_Web_Links FROM (JOURNALS AS J INNER JOIN ARTICLES AS A ON J.JOURNAL_ID = A.JOURNAL_ID) LEFT JOIN [REFERENCES] AS R ON A.ARTICLE_ID = R.ARTICLE_ID GROUP BY J.QUART, A.ARTICLE_ID)  AS Q
GROUP BY Q.QUART
ORDER BY Avg(Q.Direct_Web_Links) DESC;


--- Query: 030b_DOI_per_article_by_subject ---
SELECT Q.QUART, Count(Q.ARTICLE_ID) AS Articles, Sum(Q.DOI_Refs) AS Total_DOI_Refs, Round(Avg(Q.DOI_Refs),2) AS Mean_DOI_Refs_Per_Article
FROM (SELECT J.QUART, A.ARTICLE_ID, Nz(Sum(IIf(R.REFERENCE_TEXT Like "*doi.org/*" Or R.REFERENCE_TEXT Like "*dx.doi.org/*" Or R.REFERENCE_TEXT Like "*doi:*",1,0)),0) AS DOI_Refs FROM (JOURNALS AS J INNER JOIN ARTICLES AS A ON J.JOURNAL_ID = A.JOURNAL_ID) LEFT JOIN [REFERENCES] AS R ON A.ARTICLE_ID = R.ARTICLE_ID GROUP BY J.QUART, A.ARTICLE_ID)  AS Q
GROUP BY Q.QUART
ORDER BY Avg(Q.DOI_Refs) DESC;


--- Query: 031_URLs_by_year ---
SELECT A.P_YEAR, Count(R.REF_ID) AS Total_References, Sum(IIf(R.REFERENCE_TEXT Like "*http://*" Or R.REFERENCE_TEXT Like "*https://*" Or R.REFERENCE_TEXT Like "*www.*" Or R.REFERENCE_TEXT Like "*doi.org/*" Or R.REFERENCE_TEXT Like "*dx.doi.org/*" Or R.REFERENCE_TEXT Like "*doi:*",1,0)) AS URL_References
FROM ARTICLES AS A INNER JOIN [REFERENCES] AS R ON A.ARTICLE_ID = R.ARTICLE_ID
GROUP BY A.P_YEAR
ORDER BY A.P_YEAR;


--- Query: 031a_Direct_by_year ---
SELECT A.P_YEAR, Count(R.REF_ID) AS Total_References, Sum(IIf((R.REFERENCE_TEXT Like "*http://*" Or R.REFERENCE_TEXT Like "*https://*" Or R.REFERENCE_TEXT Like "*www.*") And R.REFERENCE_TEXT Not Like "*doi.org/*" And R.REFERENCE_TEXT Not Like "*dx.doi.org/*" And R.REFERENCE_TEXT Not Like "*doi:*",1,0)) AS Direct_Web_Links
FROM ARTICLES AS A INNER JOIN [REFERENCES] AS R ON A.ARTICLE_ID = R.ARTICLE_ID
GROUP BY A.P_YEAR
ORDER BY A.P_YEAR;


--- Query: 031b_DOI_by_year ---
SELECT A.P_YEAR, Count(R.REF_ID) AS Total_References, Sum(IIf(R.REFERENCE_TEXT Like "*doi.org/*" Or R.REFERENCE_TEXT Like "*dx.doi.org/*" Or R.REFERENCE_TEXT Like "*doi:*",1,0)) AS DOI_References
FROM ARTICLES AS A INNER JOIN [REFERENCES] AS R ON A.ARTICLE_ID = R.ARTICLE_ID
GROUP BY A.P_YEAR
ORDER BY A.P_YEAR;


--- Query: 032_URL_per_article_by_year ---
SELECT Q.P_YEAR, Count(Q.ARTICLE_ID) AS Articles, Sum(Q.URL_Refs) AS Total_URL_Refs, Round(Avg(Q.URL_Refs),2) AS Mean_URL_Refs_Per_Article
FROM (SELECT A.P_YEAR, A.ARTICLE_ID, Nz(Sum(IIf(R.REFERENCE_TEXT Like "*http://*" Or R.REFERENCE_TEXT Like "*https://*" Or R.REFERENCE_TEXT Like "*www.*" Or R.REFERENCE_TEXT Like "*doi.org/*" Or R.REFERENCE_TEXT Like "*dx.doi.org/*" Or R.REFERENCE_TEXT Like "*doi:*",1,0)),0) AS URL_Refs FROM ARTICLES AS A LEFT JOIN [REFERENCES] AS R ON A.ARTICLE_ID = R.ARTICLE_ID GROUP BY A.P_YEAR, A.ARTICLE_ID)  AS Q
GROUP BY Q.P_YEAR
ORDER BY Q.P_YEAR;


--- Query: 032a_Direct_per_article_by_year ---
SELECT Q.P_YEAR, Count(Q.ARTICLE_ID) AS Articles, Sum(Q.Direct_Web_Links) AS Total_Direct_Web_Links, Round(Avg(Q.Direct_Web_Links),2) AS Mean_Direct_Web_Links_Per_Article
FROM (SELECT A.P_YEAR, A.ARTICLE_ID, Nz(Sum(IIf((R.REFERENCE_TEXT Like "*http://*" Or R.REFERENCE_TEXT Like "*https://*" Or R.REFERENCE_TEXT Like "*www.*") And R.REFERENCE_TEXT Not Like "*doi.org/*" And R.REFERENCE_TEXT Not Like "*dx.doi.org/*" And R.REFERENCE_TEXT Not Like "*doi:*",1,0)),0) AS Direct_Web_Links FROM ARTICLES AS A LEFT JOIN [REFERENCES] AS R ON A.ARTICLE_ID = R.ARTICLE_ID GROUP BY A.P_YEAR, A.ARTICLE_ID)  AS Q
GROUP BY Q.P_YEAR
ORDER BY Q.P_YEAR;


--- Query: 032b_DOI_per_article_by_year ---
SELECT Q.P_YEAR, Count(Q.ARTICLE_ID) AS Articles, Sum(Q.DOI_Refs) AS Total_DOI_Refs, Round(Avg(Q.DOI_Refs),2) AS Mean_DOI_Refs_Per_Article
FROM (SELECT A.P_YEAR, A.ARTICLE_ID, Nz(Sum(IIf(R.REFERENCE_TEXT Like "*doi.org/*" Or R.REFERENCE_TEXT Like "*dx.doi.org/*" Or R.REFERENCE_TEXT Like "*doi:*",1,0)),0) AS DOI_Refs FROM ARTICLES AS A LEFT JOIN [REFERENCES] AS R ON A.ARTICLE_ID = R.ARTICLE_ID GROUP BY A.P_YEAR, A.ARTICLE_ID)  AS Q
GROUP BY Q.P_YEAR
ORDER BY Q.P_YEAR;


--- Query: 033_Direct_Refs_Only ---
SELECT R.REF_ID, R.REF_INDEX, R.REFERENCE_TEXT, R.ARTICLE_ID
FROM [REFERENCES] AS R
WHERE (
        R.REFERENCE_TEXT Like "*http://*"
        OR R.REFERENCE_TEXT Like "*https://*"
        OR R.REFERENCE_TEXT Like "*www.*"
    )
    AND R.REFERENCE_TEXT Not Like "*doi.org/*"
    AND R.REFERENCE_TEXT Not Like "*dx.doi.org/*"
    AND R.REFERENCE_TEXT Not Like "*doi:*";


--- Query: 033a_Direct_Refs_ALL ---
SELECT J.JOURNAL_ID, J.JOURNAL_NAME, J.SUBJECT, J.QUART, A.ARTICLE_ID, A.ARTICLE_TITLE, A.P_YEAR, R.REF_ID, R.REF_INDEX, R.REFERENCE_TEXT
FROM (JOURNALS AS J INNER JOIN ARTICLES AS A ON J.JOURNAL_ID = A.JOURNAL_ID) INNER JOIN [REFERENCES] AS R ON A.ARTICLE_ID = R.ARTICLE_ID
WHERE (
        R.REFERENCE_TEXT Like "*http://*"
        OR R.REFERENCE_TEXT Like "*https://*"
        OR R.REFERENCE_TEXT Like "*www.*"
    )
    AND R.REFERENCE_TEXT Not Like "*doi.org/*"
    AND R.REFERENCE_TEXT Not Like "*dx.doi.org/*"
    AND R.REFERENCE_TEXT Not Like "*doi:*";


--- Query: 034_direct_web_link_only ---
SELECT R.REF_ID, Mid(R.REFERENCE_TEXT,IIf(InStr(R.REFERENCE_TEXT,"http://")>0,InStr(R.REFERENCE_TEXT,"http://"),IIf(InStr(R.REFERENCE_TEXT,"https://")>0,InStr(R.REFERENCE_TEXT,"https://"),InStr(R.REFERENCE_TEXT,"www."))),200) AS URL_EXTRACT
FROM [REFERENCES] AS R
WHERE (         R.REFERENCE_TEXT Like "*http://*"         OR R.REFERENCE_TEXT Like "*https://*"         OR R.REFERENCE_TEXT Like "*www.*"     )     AND R.REFERENCE_TEXT Not Like "*doi.org/*"     AND R.REFERENCE_TEXT Not Like "*dx.doi.org/*"     AND R.REFERENCE_TEXT Not Like "*doi:*";


--- Query: 035_domains ---
SELECT Q.REF_ID, Q.ARTICLE_ID, LCase(Left(Q.URL_CLEAN,IIf(InStr(Q.URL_CLEAN,"/")>0,InStr(Q.URL_CLEAN,"/")-1,Len(Q.URL_CLEAN)))) AS [DOMAIN]
FROM (SELECT R.REF_ID, R.ARTICLE_ID, Replace(Replace(Replace(Mid(R.REFERENCE_TEXT,IIf(InStr(R.REFERENCE_TEXT,"http://")>0,InStr(R.REFERENCE_TEXT,"http://"),IIf(InStr(R.REFERENCE_TEXT,"https://")>0,InStr(R.REFERENCE_TEXT,"https://"),InStr(R.REFERENCE_TEXT,"www."))),200),"https://",""),"http://",""),"www.","") AS URL_CLEAN FROM [REFERENCES] AS R WHERE (             R.REFERENCE_TEXT Like "*http://*"             OR R.REFERENCE_TEXT Like "*https://*"             OR R.REFERENCE_TEXT Like "*www.*"         )         AND R.REFERENCE_TEXT Not Like "*doi.org/*"         AND R.REFERENCE_TEXT Not Like "*dx.doi.org/*"         AND R.REFERENCE_TEXT Not Like "*doi:*")  AS Q;


--- Query: 035a_Count direct web-link domains ---
SELECT D.DOMAIN, Count(*) AS Domain_Count
FROM (SELECT LCase(Left(Q.URL_CLEAN,IIf(InStr(Q.URL_CLEAN,"/")>0,InStr(Q.URL_CLEAN,"/")-1,Len(Q.URL_CLEAN)))) AS [DOMAIN] FROM (SELECT Replace(Replace(Replace(Mid(R.REFERENCE_TEXT,IIf(InStr(R.REFERENCE_TEXT,"http://")>0,InStr(R.REFERENCE_TEXT,"http://"),IIf(InStr(R.REFERENCE_TEXT,"https://")>0,InStr(R.REFERENCE_TEXT,"https://"),InStr(R.REFERENCE_TEXT,"www."))),200),"https://",""),"http://",""),"www.","") AS URL_CLEAN FROM [REFERENCES] AS R WHERE (                 R.REFERENCE_TEXT Like "*http://*"                 OR R.REFERENCE_TEXT Like "*https://*"                 OR R.REFERENCE_TEXT Like "*www.*"             )             AND R.REFERENCE_TEXT Not Like "*doi.org/*"             AND R.REFERENCE_TEXT Not Like "*dx.doi.org/*"             AND R.REFERENCE_TEXT Not Like "*doi:*")  AS Q)  AS D
GROUP BY D.DOMAIN
ORDER BY Count(*) DESC;


--- Query: 100_facebook ---
SELECT REF_ID, URL_EXTRACT
FROM 034_direct_web_link_only
WHERE URL_EXTRACT Like "*facebook*";


--- Query: 100a_fb_com ---
SELECT R.REF_ID, Mid(R.REFERENCE_TEXT,IIf(InStr(R.REFERENCE_TEXT,"http://")>0,InStr(R.REFERENCE_TEXT,"http://"),IIf(InStr(R.REFERENCE_TEXT,"https://")>0,InStr(R.REFERENCE_TEXT,"https://"),InStr(R.REFERENCE_TEXT,"www."))),200) AS URL_EXTRACT
FROM [REFERENCES] AS R
WHERE (         R.REFERENCE_TEXT Like "*http://*"         OR R.REFERENCE_TEXT Like "*https://*"         OR R.REFERENCE_TEXT Like "*www.*"     )     AND R.REFERENCE_TEXT Not Like "*doi.org/*"     AND R.REFERENCE_TEXT Not Like "*dx.doi.org/*"     AND R.REFERENCE_TEXT Not Like "*doi:*"     AND     (         R.REFERENCE_TEXT Like "*facebook.c*"         OR R.REFERENCE_TEXT Like "*facebook.net*"         OR R.REFERENCE_TEXT Like "*fb.com*"         OR R.REFERENCE_TEXT Like "*fb.me*"         OR R.REFERENCE_TEXT Like "*fb.watch*"         OR R.REFERENCE_TEXT Like "*fbcdn.net*"         OR R.REFERENCE_TEXT Like "*fbsbx.com*"         OR R.REFERENCE_TEXT Like "*fb.gg*"         OR R.REFERENCE_TEXT Like "*messenger.com*"     );


--- Query: 100e_DOIs_all ---
SELECT R.REF_ID, R.ARTICLE_ID, Mid(R.REFERENCE_TEXT,IIf(InStr(R.REFERENCE_TEXT,"http://")>0,InStr(R.REFERENCE_TEXT,"http://"),IIf(InStr(R.REFERENCE_TEXT,"https://")>0,InStr(R.REFERENCE_TEXT,"https://"),IIf(InStr(R.REFERENCE_TEXT,"www.")>0,InStr(R.REFERENCE_TEXT,"www."),IIf(InStr(R.REFERENCE_TEXT,"doi:")>0,InStr(R.REFERENCE_TEXT,"doi:"),InStr(R.REFERENCE_TEXT,"doi :"))))),200) AS DOI_LOCATOR
FROM [REFERENCES] AS R
WHERE R.REFERENCE_TEXT Like "*doi*org*"     OR R.REFERENCE_TEXT Like "*doi:*"     OR R.REFERENCE_TEXT Like "*doi :*";


--- Query: 100f_DOIs_and_ref_text ---
SELECT R.REF_ID, R.ARTICLE_ID, Mid(R.REFERENCE_TEXT,IIf(InStr(R.REFERENCE_TEXT,"http://")>0,InStr(R.REFERENCE_TEXT,"http://"),IIf(InStr(R.REFERENCE_TEXT,"https://")>0,InStr(R.REFERENCE_TEXT,"https://"),IIf(InStr(R.REFERENCE_TEXT,"www.")>0,InStr(R.REFERENCE_TEXT,"www."),IIf(InStr(R.REFERENCE_TEXT,"doi:")>0,InStr(R.REFERENCE_TEXT,"doi:"),InStr(R.REFERENCE_TEXT,"doi :"))))),200) AS DOI_LOCATOR, R.REFERENCE_TEXT
FROM [REFERENCES] AS R
WHERE R.REFERENCE_TEXT Like "*doi*org*"     OR R.REFERENCE_TEXT Like "*doi:*"     OR R.REFERENCE_TEXT Like "*doi :*";


--- Query: 101_URL_DOIs ---
SELECT R.REF_ID, R.ARTICLE_ID, Mid(R.REFERENCE_TEXT,IIf(InStr(R.REFERENCE_TEXT,"http://")>0,InStr(R.REFERENCE_TEXT,"http://"),IIf(InStr(R.REFERENCE_TEXT,"https://")>0,InStr(R.REFERENCE_TEXT,"https://"),InStr(R.REFERENCE_TEXT,"www."))),200) AS DOI_URL
FROM [REFERENCES] AS R
WHERE (     R.REFERENCE_TEXT Like "*http://*"     OR R.REFERENCE_TEXT Like "*https://*"     OR R.REFERENCE_TEXT Like "*www.*" ) AND (     R.REFERENCE_TEXT Like "*doi*org*" );


--- Query: 101b_URL_DOIs_as_strings ---
SELECT R.REF_ID, R.ARTICLE_ID, Mid(R.REFERENCE_TEXT,IIf(InStr(R.REFERENCE_TEXT,"doi:")>0,InStr(R.REFERENCE_TEXT,"doi:"),InStr(R.REFERENCE_TEXT,"doi :")),120) AS DOI_STRING
FROM [REFERENCES] AS R
WHERE (     R.REFERENCE_TEXT Like "*doi:*"     OR R.REFERENCE_TEXT Like "*doi :*" ) AND NOT (     R.REFERENCE_TEXT Like "*http://*"     OR R.REFERENCE_TEXT Like "*https://*"     OR R.REFERENCE_TEXT Like "*www.*" );


--- Query: 110_YOUTUBE ---
SELECT J.JOURNAL_NAME, A.ARTICLE_TITLE, J.SUBJECT, A.P_YEAR, R.REF_INDEX, T.Extracted_URL
FROM ((Total_Direct_Web_Links AS T INNER JOIN ARTICLES AS A ON T.ARTICLE_ID = A.ARTICLE_ID) INNER JOIN JOURNALS AS J ON A.JOURNAL_ID = J.JOURNAL_ID) INNER JOIN [REFERENCES] AS R ON T.REF_ID = R.REF_ID
WHERE T.Extracted_URL LIKE "*youtube.com*"
    OR T.Extracted_URL LIKE "*youtu.be*"
ORDER BY J.JOURNAL_ID, A.P_YEAR, A.ARTICLE_TITLE;


--- Query: 110_YouTube_DWL ---
SELECT J.JOURNAL_NAME, A.ARTICLE_TITLE, J.SUBJECT, A.P_YEAR, R.REFERENCE_TEXT, R.REF_INDEX, T.Extracted_URL
FROM ((Total_Direct_Web_Links AS T INNER JOIN ARTICLES AS A ON T.ARTICLE_ID = A.ARTICLE_ID) INNER JOIN JOURNALS AS J ON A.JOURNAL_ID = J.JOURNAL_ID) INNER JOIN [REFERENCES] AS R ON T.REF_ID = R.REF_ID
WHERE T.Extracted_URL LIKE "*youtube.com*"
    OR T.Extracted_URL LIKE "*youtu.be*"
ORDER BY J.JOURNAL_ID, A.P_YEAR, A.ARTICLE_TITLE;


--- Query: 110_youtube_TOT ---
SELECT J.JOURNAL_NAME, A.ARTICLE_ID, A.ARTICLE_TITLE, J.SUBJECT, A.P_YEAR, R.REF_INDEX, T.Extracted_URL
FROM ((TOTAL_REFS AS T INNER JOIN ARTICLES AS A ON T.ARTICLE_ID = A.ARTICLE_ID) INNER JOIN JOURNALS AS J ON A.JOURNAL_ID = J.JOURNAL_ID) INNER JOIN [REFERENCES] AS R ON T.REF_ID = R.REF_ID
WHERE T.Extracted_URL LIKE "*youtube.com*"
    OR T.Extracted_URL LIKE "*youtu.be*"
ORDER BY J.JOURNAL_ID, A.P_YEAR, A.ARTICLE_TITLE;


--- Query: 110a_YOUTUBE_JOUR_ART_SUB_QUAR_YEAR ---
SELECT J.JOURNAL_NAME, A.ARTICLE_ID, J.SUBJECT, J.QUART, A.P_YEAR, R.REF_INDEX
FROM ((Total_Direct_Web_Links AS T INNER JOIN ARTICLES AS A ON T.ARTICLE_ID = A.ARTICLE_ID) INNER JOIN JOURNALS AS J ON A.JOURNAL_ID = J.JOURNAL_ID) INNER JOIN [REFERENCES] AS R ON T.REF_ID = R.REF_ID
WHERE T.Extracted_URL LIKE "*youtube.com*" 
    OR T.Extracted_URL LIKE "*youtu.be*"
ORDER BY J.QUART, A.P_YEAR, J.JOURNAL_NAME;


--- Query: 110b_YOUTUBE_QUARTxYEAR ---
SELECT J.QUART, A.P_YEAR, COUNT(*) AS YouTube_Refs
FROM (Total_Direct_Web_Links AS T INNER JOIN ARTICLES AS A ON T.ARTICLE_ID = A.ARTICLE_ID) INNER JOIN JOURNALS AS J ON A.JOURNAL_ID = J.JOURNAL_ID
WHERE T.Extracted_URL LIKE "*youtube.com*" 
    OR T.Extracted_URL LIKE "*youtu.be*"
GROUP BY J.QUART, A.P_YEAR
ORDER BY J.QUART, A.P_YEAR;


--- Query: 111_2_YT_by_journal ---
SELECT J.JOURNAL_ID, J.JOURNAL_NAME, Nz(Y.YouTube_URL_Occurrences,0) AS [YouTube URL Occurrences], Nz(Ac.Total_Articles,0) AS [Total Articles], Nz(D.Total_DWL,0) AS [Total DWL], IIf(Nz(Ac.Total_Articles,0)=0,0,Round(Nz(Y.YouTube_URL_Occurrences,0)*100.0/Nz(Ac.Total_Articles,0),2)) AS [% of total Articles], IIf(Nz(D.Total_DWL,0)=0,0,Round(Nz(Y.YouTube_URL_Occurrences,0)*100.0/Nz(D.Total_DWL,0),2)) AS [% of DWL], IIf(Nz(Y.YouTube_URL_Occurrences,0)=0,Null,Round(Nz(Ac.Total_Articles,0)*1.0/Nz(Y.YouTube_URL_Occurrences,0),1)) AS [Frequency (1 in X articles)]
FROM ((JOURNALS AS J LEFT JOIN (SELECT 
                    A.JOURNAL_ID,
                    Count(*) AS YouTube_URL_Occurrences
                FROM 
                    Total_Direct_Web_Links AS T
                    INNER JOIN ARTICLES AS A
                        ON T.ARTICLE_ID = A.ARTICLE_ID
                WHERE 
                    LCase(T.Extracted_URL) LIKE "*youtube.com*"
                    OR LCase(T.Extracted_URL) LIKE "*youtu.be*"
                    OR LCase(T.Extracted_URL) LIKE "*youtube-nocookie.com*"
                GROUP BY 
                    A.JOURNAL_ID
            )  AS Y ON J.JOURNAL_ID = Y.JOURNAL_ID) LEFT JOIN (SELECT 
                JOURNAL_ID,
                Count(*) AS Total_Articles
            FROM 
                ARTICLES
            GROUP BY 
                JOURNAL_ID
        )  AS Ac ON J.JOURNAL_ID = Ac.JOURNAL_ID) LEFT JOIN (SELECT 
            A.JOURNAL_ID,
            Count(*) AS Total_DWL
        FROM 
            Total_Direct_Web_Links AS T
            INNER JOIN ARTICLES AS A
                ON T.ARTICLE_ID = A.ARTICLE_ID
        GROUP BY 
            A.JOURNAL_ID
    )  AS D ON J.JOURNAL_ID = D.JOURNAL_ID
ORDER BY Nz(Y.YouTube_URL_Occurrences,0) DESC , J.JOURNAL_ID;


--- Query: 111_YT_by journal ---
SELECT J.JOURNAL_ID, J.JOURNAL_NAME, Nz(C.YouTube_URL_Occurrences,0) AS YouTube_URL_Occurrences
FROM JOURNALS AS J LEFT JOIN (SELECT 
            A.JOURNAL_ID,
            Count(*) AS YouTube_URL_Occurrences
        FROM 
            Total_Direct_Web_Links AS T
            INNER JOIN ARTICLES AS A
                ON T.ARTICLE_ID = A.ARTICLE_ID
        WHERE 
            LCase(T.Extracted_URL) LIKE "*youtube.com*"
            OR LCase(T.Extracted_URL) LIKE "*youtu.be*"
            OR LCase(T.Extracted_URL) LIKE "*youtube-nocookie.com*"
        GROUP BY 
            A.JOURNAL_ID
    )  AS C ON J.JOURNAL_ID = C.JOURNAL_ID
ORDER BY J.JOURNAL_ID;


--- Query: 111a_YT_by_subject ---
SELECT J.SUBJECT, Count(*) AS YouTube_URL_Occurrences
FROM (Total_Direct_Web_Links AS T INNER JOIN ARTICLES AS A ON T.ARTICLE_ID = A.ARTICLE_ID) INNER JOIN JOURNALS AS J ON A.JOURNAL_ID = J.JOURNAL_ID
WHERE LCase(T.Extracted_URL) LIKE "*youtube.com*"
    OR LCase(T.Extracted_URL) LIKE "*youtu.be*"
    OR LCase(T.Extracted_URL) LIKE "*youtube-nocookie.com*"
GROUP BY J.SUBJECT
ORDER BY Count(*) DESC;


--- Query: 111b_YT_by_quart ---
SELECT J.QUART, Count(*) AS YouTube_URL_Occurrences
FROM (Total_Direct_Web_Links AS T INNER JOIN ARTICLES AS A ON T.ARTICLE_ID = A.ARTICLE_ID) INNER JOIN JOURNALS AS J ON A.JOURNAL_ID = J.JOURNAL_ID
WHERE LCase(T.Extracted_URL) LIKE "*youtube.com*"
    OR LCase(T.Extracted_URL) LIKE "*youtu.be*"
    OR LCase(T.Extracted_URL) LIKE "*youtube-nocookie.com*"
GROUP BY J.QUART
ORDER BY J.QUART;


--- Query: 111c_YT_by_year ---
SELECT A.P_YEAR, Count(*) AS YouTube_URL_Occurrences
FROM Total_Direct_Web_Links AS T INNER JOIN ARTICLES AS A ON T.ARTICLE_ID = A.ARTICLE_ID
WHERE LCase(T.Extracted_URL) LIKE "*youtube.com*"
    OR LCase(T.Extracted_URL) LIKE "*youtu.be*"
    OR LCase(T.Extracted_URL) LIKE "*youtube-nocookie.com*"
GROUP BY A.P_YEAR
ORDER BY A.P_YEAR;


--- Query: 120_FB ---
SELECT J.JOURNAL_NAME, A.ARTICLE_ID, A.ARTICLE_TITLE, J.SUBJECT, A.P_YEAR, R.REF_INDEX, T.Extracted_URL
FROM ((Total_Direct_Web_Links AS T INNER JOIN ARTICLES AS A ON T.ARTICLE_ID = A.ARTICLE_ID) INNER JOIN JOURNALS AS J ON A.JOURNAL_ID = J.JOURNAL_ID) INNER JOIN [REFERENCES] AS R ON T.REF_ID = R.REF_ID
WHERE LCase(T.Extracted_URL) LIKE "*facebook.c*"
    OR LCase(T.Extracted_URL) LIKE "*fb.com*"
    OR LCase(T.Extracted_URL) LIKE "*fb.watch*"
    OR LCase(T.Extracted_URL) LIKE "*fb.gg*"
    OR LCase(T.Extracted_URL) LIKE "*fbcdn.net*"
    OR LCase(T.Extracted_URL) LIKE "*fbsbx.com*"
ORDER BY J.JOURNAL_ID, A.P_YEAR, A.ARTICLE_TITLE;


--- Query: 120_Twitter_mix ---
SELECT J.JOURNAL_NAME, A.ARTICLE_ID, A.ARTICLE_TITLE, J.SUBJECT, A.P_YEAR, R.REF_INDEX, T.Extracted_URL
FROM ((Total_Direct_Web_Links AS T INNER JOIN ARTICLES AS A ON T.ARTICLE_ID = A.ARTICLE_ID) INNER JOIN JOURNALS AS J ON A.JOURNAL_ID = J.JOURNAL_ID) INNER JOIN [REFERENCES] AS R ON T.REF_ID = R.REF_ID
WHERE (
        LCase(T.Extracted_URL) LIKE "*twitter.com/*"
        OR LCase(T.Extracted_URL) LIKE "*twitter.com?*"
        OR LCase(T.Extracted_URL) LIKE "*://x.com/*"
        OR LCase(T.Extracted_URL) LIKE "*://www.x.com/*"
        OR LCase(T.Extracted_URL) LIKE "*://t.co/*"
    )
    AND LCase(T.Extracted_URL) NOT LIKE "*utm_source=twitter*"
ORDER BY J.JOURNAL_ID, A.P_YEAR, A.ARTICLE_TITLE;


--- Query: 120a_FB_by_journal ---
SELECT J.JOURNAL_ID, J.JOURNAL_NAME, Nz(C.Facebook_URL_Occurrences,0) AS Facebook_URL_Occurrences
FROM JOURNALS AS J LEFT JOIN (SELECT 
            A.JOURNAL_ID,
            Count(*) AS Facebook_URL_Occurrences
        FROM 
            Total_Direct_Web_Links AS T
            INNER JOIN ARTICLES AS A
                ON T.ARTICLE_ID = A.ARTICLE_ID
        WHERE 
            LCase(T.Extracted_URL) LIKE "*facebook.c*"
            OR LCase(T.Extracted_URL) LIKE "*fb.com*"
            OR LCase(T.Extracted_URL) LIKE "*fb.watch*"
            OR LCase(T.Extracted_URL) LIKE "*fb.gg*"
            OR LCase(T.Extracted_URL) LIKE "*fbcdn.net*"
            OR LCase(T.Extracted_URL) LIKE "*fbsbx.com*"
        GROUP BY 
            A.JOURNAL_ID
    )  AS C ON J.JOURNAL_ID = C.JOURNAL_ID
ORDER BY J.JOURNAL_ID;


--- Query: 120a_Twitter_by_Journal ---
SELECT J.JOURNAL_ID, J.JOURNAL_NAME, Nz(C.Twitter_URL_Occurrences,0) AS Twitter_URL_Occurrences
FROM JOURNALS AS J LEFT JOIN (SELECT 
            A.JOURNAL_ID,
            Count(*) AS Twitter_URL_Occurrences
        FROM 
            Total_Direct_Web_Links AS T
            INNER JOIN ARTICLES AS A
                ON T.ARTICLE_ID = A.ARTICLE_ID
        WHERE 
            (
                LCase(T.Extracted_URL) LIKE "*twitter.com/*"
                OR LCase(T.Extracted_URL) LIKE "*twitter.com?*"
                OR LCase(T.Extracted_URL) LIKE "*://x.com/*"
                OR LCase(T.Extracted_URL) LIKE "*://www.x.com/*"
                OR LCase(T.Extracted_URL) LIKE "*://t.co/*"
            )
            AND LCase(T.Extracted_URL) NOT LIKE "*utm_source=twitter*"
        GROUP BY 
            A.JOURNAL_ID
    )  AS C ON J.JOURNAL_ID = C.JOURNAL_ID
ORDER BY J.JOURNAL_ID;


--- Query: 120b_FB_by_subject ---
SELECT J.SUBJECT, Count(*) AS FB_URL_Occurrences
FROM (Total_Direct_Web_Links AS T INNER JOIN ARTICLES AS A ON T.ARTICLE_ID = A.ARTICLE_ID) INNER JOIN JOURNALS AS J ON A.JOURNAL_ID = J.JOURNAL_ID
WHERE LCase(T.Extracted_URL) LIKE "*facebook.c*"
    OR LCase(T.Extracted_URL) LIKE "*fb.com*"
    OR LCase(T.Extracted_URL) LIKE "*fb.watch*"
    OR LCase(T.Extracted_URL) LIKE "*fb.gg*"
    OR LCase(T.Extracted_URL) LIKE "*fbcdn.net*"
    OR LCase(T.Extracted_URL) LIKE "*fbsbx.com*"
GROUP BY J.SUBJECT
ORDER BY Count(*) DESC;


--- Query: 120b_Twitter_by_Subject ---
SELECT J.SUBJECT, Count(*) AS Twitter_URL_Occurrences
FROM (Total_Direct_Web_Links AS T INNER JOIN ARTICLES AS A ON T.ARTICLE_ID = A.ARTICLE_ID) INNER JOIN JOURNALS AS J ON A.JOURNAL_ID = J.JOURNAL_ID
WHERE (
        LCase(T.Extracted_URL) LIKE "*twitter.com/*"
        OR LCase(T.Extracted_URL) LIKE "*twitter.com?*"
        OR LCase(T.Extracted_URL) LIKE "*://x.com/*"
        OR LCase(T.Extracted_URL) LIKE "*://www.x.com/*"
        OR LCase(T.Extracted_URL) LIKE "*://t.co/*"
    )
    AND LCase(T.Extracted_URL) NOT LIKE "*utm_source=twitter*"
GROUP BY J.SUBJECT
ORDER BY Count(*) DESC;


--- Query: 120c_FB_by_quart ---
SELECT J.QUART, Count(*) AS FB_URL_Occurrences
FROM (Total_Direct_Web_Links AS T INNER JOIN ARTICLES AS A ON T.ARTICLE_ID = A.ARTICLE_ID) INNER JOIN JOURNALS AS J ON A.JOURNAL_ID = J.JOURNAL_ID
WHERE LCase(T.Extracted_URL) LIKE "*facebook.c*"
    OR LCase(T.Extracted_URL) LIKE "*fb.com*"
    OR LCase(T.Extracted_URL) LIKE "*fb.watch*"
    OR LCase(T.Extracted_URL) LIKE "*fb.gg*"
    OR LCase(T.Extracted_URL) LIKE "*fbcdn.net*"
    OR LCase(T.Extracted_URL) LIKE "*fbsbx.com*"
GROUP BY J.QUART
ORDER BY J.QUART;


--- Query: 120c_Twitter_by_Quart ---
SELECT J.QUART, Count(*) AS Twitter_URL_Occurrences
FROM (Total_Direct_Web_Links AS T INNER JOIN ARTICLES AS A ON T.ARTICLE_ID = A.ARTICLE_ID) INNER JOIN JOURNALS AS J ON A.JOURNAL_ID = J.JOURNAL_ID
WHERE (
        LCase(T.Extracted_URL) LIKE "*twitter.com/*"
        OR LCase(T.Extracted_URL) LIKE "*twitter.com?*"
        OR LCase(T.Extracted_URL) LIKE "*://x.com/*"
        OR LCase(T.Extracted_URL) LIKE "*://www.x.com/*"
        OR LCase(T.Extracted_URL) LIKE "*://t.co/*"
    )
    AND LCase(T.Extracted_URL) NOT LIKE "*utm_source=twitter*"
GROUP BY J.QUART
ORDER BY J.QUART;


--- Query: 120d_FB_by_Year ---
SELECT A.P_YEAR, Count(*) AS FB_URL_Occurrences
FROM Total_Direct_Web_Links AS T INNER JOIN ARTICLES AS A ON T.ARTICLE_ID = A.ARTICLE_ID
WHERE LCase(T.Extracted_URL) LIKE "*facebook.c*"
    OR LCase(T.Extracted_URL) LIKE "*fb.com*"
    OR LCase(T.Extracted_URL) LIKE "*fb.watch*"
    OR LCase(T.Extracted_URL) LIKE "*fb.gg*"
    OR LCase(T.Extracted_URL) LIKE "*fbcdn.net*"
    OR LCase(T.Extracted_URL) LIKE "*fbsbx.com*"
GROUP BY A.P_YEAR
ORDER BY A.P_YEAR;


--- Query: 120d_Twitter_by_Year ---
SELECT A.P_YEAR, Count(*) AS Twitter_URL_Occurrences
FROM Total_Direct_Web_Links AS T INNER JOIN ARTICLES AS A ON T.ARTICLE_ID = A.ARTICLE_ID
WHERE (
        LCase(T.Extracted_URL) LIKE "*twitter.com/*"
        OR LCase(T.Extracted_URL) LIKE "*twitter.com?*"
        OR LCase(T.Extracted_URL) LIKE "*://x.com/*"
        OR LCase(T.Extracted_URL) LIKE "*://www.x.com/*"
        OR LCase(T.Extracted_URL) LIKE "*://t.co/*"
    )
    AND LCase(T.Extracted_URL) NOT LIKE "*utm_source=twitter*"
GROUP BY A.P_YEAR
ORDER BY A.P_YEAR;


--- Query: 150_SM_misc ---
SELECT IIf(LCase(T.Extracted_URL) Like "*instagram.com*" Or LCase(T.Extracted_URL) Like "*instagr.am*", "Instagram",
    IIf(LCase(T.Extracted_URL) Like "*linkedin.com*" Or LCase(T.Extracted_URL) Like "*lnkd.in*", "LinkedIn",
    IIf(LCase(T.Extracted_URL) Like "*reddit.com*" Or LCase(T.Extracted_URL) Like "*redd.it*", "Reddit",
    IIf(LCase(T.Extracted_URL) Like "*tiktok.com*" Or LCase(T.Extracted_URL) Like "*vm.tiktok.com*" Or LCase(T.Extracted_URL) Like "*vt.tiktok.com*", "TikTok",
    IIf(LCase(T.Extracted_URL) Like "*pinterest.com*" Or LCase(T.Extracted_URL) Like "*pin.it*", "Pinterest",
    IIf(LCase(T.Extracted_URL) Like "*snapchat.com*" Or LCase(T.Extracted_URL) Like "*story.snapchat.com*", "Snapchat",
    IIf(LCase(T.Extracted_URL) Like "*quora.com*", "Quora",
    IIf(LCase(T.Extracted_URL) Like "*threads.net*", "Threads",
    IIf(LCase(T.Extracted_URL) Like "*wechat.com*" Or LCase(T.Extracted_URL) Like "*weixin.qq.com*", "WeChat",
    IIf(LCase(T.Extracted_URL) Like "*weibo.com*", "Weibo","")))))))))) AS SM_Platform, J.JOURNAL_NAME, A.ARTICLE_ID, A.ARTICLE_TITLE, J.SUBJECT, A.P_YEAR, R.REF_INDEX, T.Extracted_URL
FROM ((Total_Direct_Web_Links AS T INNER JOIN ARTICLES AS A ON T.ARTICLE_ID = A.ARTICLE_ID) INNER JOIN JOURNALS AS J ON A.JOURNAL_ID = J.JOURNAL_ID) INNER JOIN [REFERENCES] AS R ON T.REF_ID = R.REF_ID
WHERE LCase(T.Extracted_URL) Like "*instagram.com*"
    Or LCase(T.Extracted_URL) Like "*instagr.am*"
    Or LCase(T.Extracted_URL) Like "*linkedin.com*"
    Or LCase(T.Extracted_URL) Like "*lnkd.in*"
    Or LCase(T.Extracted_URL) Like "*reddit.com*"
    Or LCase(T.Extracted_URL) Like "*redd.it*"
    Or LCase(T.Extracted_URL) Like "*tiktok.com*"
    Or LCase(T.Extracted_URL) Like "*vm.tiktok.com*"
    Or LCase(T.Extracted_URL) Like "*vt.tiktok.com*"
    Or LCase(T.Extracted_URL) Like "*pinterest.com*"
    Or LCase(T.Extracted_URL) Like "*pin.it*"
    Or LCase(T.Extracted_URL) Like "*snapchat.com*"
    Or LCase(T.Extracted_URL) Like "*story.snapchat.com*"
    Or LCase(T.Extracted_URL) Like "*quora.com*"
    Or LCase(T.Extracted_URL) Like "*threads.net*"
    Or LCase(T.Extracted_URL) Like "*wechat.com*"
    Or LCase(T.Extracted_URL) Like "*weixin.qq.com*"
    Or LCase(T.Extracted_URL) Like "*weibo.com*"
ORDER BY J.JOURNAL_ID, A.P_YEAR, A.ARTICLE_TITLE;


--- Query: 150a_SM_misc_by_Journal ---
SELECT J.JOURNAL_ID, J.JOURNAL_NAME, C.SM_Platform, Nz(C.SM_URL_Occurrences,0) AS SM_URL_Occurrences
FROM JOURNALS AS J INNER JOIN (SELECT 
            A.JOURNAL_ID,
            IIf(LCase(T.Extracted_URL) Like "*instagram.com*" Or LCase(T.Extracted_URL) Like "*instagr.am*", "Instagram",
            IIf(LCase(T.Extracted_URL) Like "*linkedin.com*" Or LCase(T.Extracted_URL) Like "*lnkd.in*", "LinkedIn",
            IIf(LCase(T.Extracted_URL) Like "*reddit.com*" Or LCase(T.Extracted_URL) Like "*redd.it*", "Reddit",
            IIf(LCase(T.Extracted_URL) Like "*tiktok.com*" Or LCase(T.Extracted_URL) Like "*vm.tiktok.com*" Or LCase(T.Extracted_URL) Like "*vt.tiktok.com*", "TikTok",
            IIf(LCase(T.Extracted_URL) Like "*pinterest.com*" Or LCase(T.Extracted_URL) Like "*pin.it*", "Pinterest",
            IIf(LCase(T.Extracted_URL) Like "*snapchat.com*" Or LCase(T.Extracted_URL) Like "*story.snapchat.com*", "Snapchat",
            IIf(LCase(T.Extracted_URL) Like "*quora.com*", "Quora",
            IIf(LCase(T.Extracted_URL) Like "*threads.net*", "Threads",
            IIf(LCase(T.Extracted_URL) Like "*wechat.com*" Or LCase(T.Extracted_URL) Like "*weixin.qq.com*", "WeChat",
            IIf(LCase(T.Extracted_URL) Like "*weibo.com*", "Weibo","")))))))))) AS SM_Platform,
            Count(*) AS SM_URL_Occurrences
        FROM 
            Total_Direct_Web_Links AS T
            INNER JOIN ARTICLES AS A ON T.ARTICLE_ID = A.ARTICLE_ID
        WHERE 
            LCase(T.Extracted_URL) Like "*instagram.com*"
            Or LCase(T.Extracted_URL) Like "*instagr.am*"
            Or LCase(T.Extracted_URL) Like "*linkedin.com*"
            Or LCase(T.Extracted_URL) Like "*lnkd.in*"
            Or LCase(T.Extracted_URL) Like "*reddit.com*"
            Or LCase(T.Extracted_URL) Like "*redd.it*"
            Or LCase(T.Extracted_URL) Like "*tiktok.com*"
            Or LCase(T.Extracted_URL) Like "*vm.tiktok.com*"
            Or LCase(T.Extracted_URL) Like "*vt.tiktok.com*"
            Or LCase(T.Extracted_URL) Like "*pinterest.com*"
            Or LCase(T.Extracted_URL) Like "*pin.it*"
            Or LCase(T.Extracted_URL) Like "*snapchat.com*"
            Or LCase(T.Extracted_URL) Like "*story.snapchat.com*"
            Or LCase(T.Extracted_URL) Like "*quora.com*"
            Or LCase(T.Extracted_URL) Like "*threads.net*"
            Or LCase(T.Extracted_URL) Like "*wechat.com*"
            Or LCase(T.Extracted_URL) Like "*weixin.qq.com*"
            Or LCase(T.Extracted_URL) Like "*weibo.com*"
        GROUP BY 
            A.JOURNAL_ID,
            IIf(LCase(T.Extracted_URL) Like "*instagram.com*" Or LCase(T.Extracted_URL) Like "*instagr.am*", "Instagram",
            IIf(LCase(T.Extracted_URL) Like "*linkedin.com*" Or LCase(T.Extracted_URL) Like "*lnkd.in*", "LinkedIn",
            IIf(LCase(T.Extracted_URL) Like "*reddit.com*" Or LCase(T.Extracted_URL) Like "*redd.it*", "Reddit",
            IIf(LCase(T.Extracted_URL) Like "*tiktok.com*" Or LCase(T.Extracted_URL) Like "*vm.tiktok.com*" Or LCase(T.Extracted_URL) Like "*vt.tiktok.com*", "TikTok",
            IIf(LCase(T.Extracted_URL) Like "*pinterest.com*" Or LCase(T.Extracted_URL) Like "*pin.it*", "Pinterest",
            IIf(LCase(T.Extracted_URL) Like "*snapchat.com*" Or LCase(T.Extracted_URL) Like "*story.snapchat.com*", "Snapchat",
            IIf(LCase(T.Extracted_URL) Like "*quora.com*", "Quora",
            IIf(LCase(T.Extracted_URL) Like "*threads.net*", "Threads",
            IIf(LCase(T.Extracted_URL) Like "*wechat.com*" Or LCase(T.Extracted_URL) Like "*weixin.qq.com*", "WeChat",
            IIf(LCase(T.Extracted_URL) Like "*weibo.com*", "Weibo",""))))))))))
    )  AS C ON J.JOURNAL_ID = C.JOURNAL_ID
ORDER BY C.SM_Platform, J.JOURNAL_ID;


--- Query: 150aa_Misc_SM_Journal_separate ---
SELECT J.JOURNAL_ID, J.JOURNAL_NAME, Nz(C.Instagram,0) AS Instagram, Nz(C.LinkedIn,0) AS LinkedIn, Nz(C.Reddit,0) AS Reddit, Nz(C.TikTok,0) AS TikTok, Nz(C.Pinterest,0) AS Pinterest, Nz(C.Snapchat,0) AS Snapchat, Nz(C.Quora,0) AS Quora, Nz(C.Threads,0) AS Threads, Nz(C.WeChat,0) AS WeChat, Nz(C.Weibo,0) AS Weibo
FROM JOURNALS AS J LEFT JOIN (SELECT 
            A.JOURNAL_ID,
            Sum(IIf(LCase(Nz(T.Extracted_URL,"")) Like "*instagram.com*" Or LCase(Nz(T.Extracted_URL,"")) Like "*instagr.am*",1,0)) AS Instagram,
            Sum(IIf(LCase(Nz(T.Extracted_URL,"")) Like "*linkedin.com*" Or LCase(Nz(T.Extracted_URL,"")) Like "*lnkd.in*",1,0)) AS LinkedIn,
            Sum(IIf(LCase(Nz(T.Extracted_URL,"")) Like "*reddit.com*" Or LCase(Nz(T.Extracted_URL,"")) Like "*redd.it*",1,0)) AS Reddit,
            Sum(IIf(LCase(Nz(T.Extracted_URL,"")) Like "*tiktok.com*" Or LCase(Nz(T.Extracted_URL,"")) Like "*vm.tiktok.com*" Or LCase(Nz(T.Extracted_URL,"")) Like "*vt.tiktok.com*",1,0)) AS TikTok,
            Sum(IIf(LCase(Nz(T.Extracted_URL,"")) Like "*pinterest.com*" Or LCase(Nz(T.Extracted_URL,"")) Like "*pin.it*",1,0)) AS Pinterest,
            Sum(IIf(LCase(Nz(T.Extracted_URL,"")) Like "*snapchat.com*" Or LCase(Nz(T.Extracted_URL,"")) Like "*story.snapchat.com*",1,0)) AS Snapchat,
            Sum(IIf(LCase(Nz(T.Extracted_URL,"")) Like "*quora.com*",1,0)) AS Quora,
            Sum(IIf(LCase(Nz(T.Extracted_URL,"")) Like "*threads.net*",1,0)) AS Threads,
            Sum(IIf(LCase(Nz(T.Extracted_URL,"")) Like "*wechat.com*" Or LCase(Nz(T.Extracted_URL,"")) Like "*weixin.qq.com*",1,0)) AS WeChat,
            Sum(IIf(LCase(Nz(T.Extracted_URL,"")) Like "*weibo.com*",1,0)) AS Weibo
        FROM 
            ARTICLES AS A
            LEFT JOIN Total_Direct_Web_Links AS T
                ON A.ARTICLE_ID = T.ARTICLE_ID
        GROUP BY 
            A.JOURNAL_ID
    )  AS C ON J.JOURNAL_ID = C.JOURNAL_ID
ORDER BY J.JOURNAL_ID;


--- Query: 150bb_MISC_SM_Subj_Separ ---
SELECT J.SUBJECT, Nz(C.Instagram,0) AS Instagram, Nz(C.LinkedIn,0) AS LinkedIn, Nz(C.Reddit,0) AS Reddit, Nz(C.TikTok,0) AS TikTok, Nz(C.Pinterest,0) AS Pinterest, Nz(C.Snapchat,0) AS Snapchat, Nz(C.Quora,0) AS Quora, Nz(C.Threads,0) AS Threads, Nz(C.WeChat,0) AS WeChat, Nz(C.Weibo,0) AS Weibo
FROM (SELECT DISTINCT SUBJECT FROM JOURNALS)  AS J LEFT JOIN (SELECT 
            J.SUBJECT,
            Sum(IIf(LCase(Nz(T.Extracted_URL,"")) Like "*instagram.com*" Or LCase(Nz(T.Extracted_URL,"")) Like "*instagr.am*",1,0)) AS Instagram,
            Sum(IIf(LCase(Nz(T.Extracted_URL,"")) Like "*linkedin.com*" Or LCase(Nz(T.Extracted_URL,"")) Like "*lnkd.in*",1,0)) AS LinkedIn,
            Sum(IIf(LCase(Nz(T.Extracted_URL,"")) Like "*reddit.com*" Or LCase(Nz(T.Extracted_URL,"")) Like "*redd.it*",1,0)) AS Reddit,
            Sum(IIf(LCase(Nz(T.Extracted_URL,"")) Like "*tiktok.com*" Or LCase(Nz(T.Extracted_URL,"")) Like "*vm.tiktok.com*" Or LCase(Nz(T.Extracted_URL,"")) Like "*vt.tiktok.com*",1,0)) AS TikTok,
            Sum(IIf(LCase(Nz(T.Extracted_URL,"")) Like "*pinterest.com*" Or LCase(Nz(T.Extracted_URL,"")) Like "*pin.it*",1,0)) AS Pinterest,
            Sum(IIf(LCase(Nz(T.Extracted_URL,"")) Like "*snapchat.com*" Or LCase(Nz(T.Extracted_URL,"")) Like "*story.snapchat.com*",1,0)) AS Snapchat,
            Sum(IIf(LCase(Nz(T.Extracted_URL,"")) Like "*quora.com*",1,0)) AS Quora,
            Sum(IIf(LCase(Nz(T.Extracted_URL,"")) Like "*threads.net*",1,0)) AS Threads,
            Sum(IIf(LCase(Nz(T.Extracted_URL,"")) Like "*wechat.com*" Or LCase(Nz(T.Extracted_URL,"")) Like "*weixin.qq.com*",1,0)) AS WeChat,
            Sum(IIf(LCase(Nz(T.Extracted_URL,"")) Like "*weibo.com*",1,0)) AS Weibo
        FROM 
            (ARTICLES AS A
            INNER JOIN JOURNALS AS J ON A.JOURNAL_ID = J.JOURNAL_ID)
            LEFT JOIN Total_Direct_Web_Links AS T
                ON A.ARTICLE_ID = T.ARTICLE_ID
        GROUP BY 
            J.SUBJECT
    )  AS C ON J.SUBJECT = C.SUBJECT
ORDER BY J.SUBJECT;


--- Query: 150cc_MISC_SM_Quart_Separ ---
SELECT J.QUART, Nz(C.Instagram,0) AS Instagram, Nz(C.LinkedIn,0) AS LinkedIn, Nz(C.Reddit,0) AS Reddit, Nz(C.TikTok,0) AS TikTok, Nz(C.Pinterest,0) AS Pinterest, Nz(C.Snapchat,0) AS Snapchat, Nz(C.Quora,0) AS Quora, Nz(C.Threads,0) AS Threads, Nz(C.WeChat,0) AS WeChat, Nz(C.Weibo,0) AS Weibo
FROM (SELECT DISTINCT QUART FROM JOURNALS)  AS J LEFT JOIN (SELECT 
            J.QUART,
            Sum(IIf(LCase(Nz(T.Extracted_URL,"")) Like "*instagram.com*" Or LCase(Nz(T.Extracted_URL,"")) Like "*instagr.am*",1,0)) AS Instagram,
            Sum(IIf(LCase(Nz(T.Extracted_URL,"")) Like "*linkedin.com*" Or LCase(Nz(T.Extracted_URL,"")) Like "*lnkd.in*",1,0)) AS LinkedIn,
            Sum(IIf(LCase(Nz(T.Extracted_URL,"")) Like "*reddit.com*" Or LCase(Nz(T.Extracted_URL,"")) Like "*redd.it*",1,0)) AS Reddit,
            Sum(IIf(LCase(Nz(T.Extracted_URL,"")) Like "*tiktok.com*" Or LCase(Nz(T.Extracted_URL,"")) Like "*vm.tiktok.com*" Or LCase(Nz(T.Extracted_URL,"")) Like "*vt.tiktok.com*",1,0)) AS TikTok,
            Sum(IIf(LCase(Nz(T.Extracted_URL,"")) Like "*pinterest.com*" Or LCase(Nz(T.Extracted_URL,"")) Like "*pin.it*",1,0)) AS Pinterest,
            Sum(IIf(LCase(Nz(T.Extracted_URL,"")) Like "*snapchat.com*" Or LCase(Nz(T.Extracted_URL,"")) Like "*story.snapchat.com*",1,0)) AS Snapchat,
            Sum(IIf(LCase(Nz(T.Extracted_URL,"")) Like "*quora.com*",1,0)) AS Quora,
            Sum(IIf(LCase(Nz(T.Extracted_URL,"")) Like "*threads.net*",1,0)) AS Threads,
            Sum(IIf(LCase(Nz(T.Extracted_URL,"")) Like "*wechat.com*" Or LCase(Nz(T.Extracted_URL,"")) Like "*weixin.qq.com*",1,0)) AS WeChat,
            Sum(IIf(LCase(Nz(T.Extracted_URL,"")) Like "*weibo.com*",1,0)) AS Weibo
        FROM 
            (ARTICLES AS A
            INNER JOIN JOURNALS AS J ON A.JOURNAL_ID = J.JOURNAL_ID)
            LEFT JOIN Total_Direct_Web_Links AS T
                ON A.ARTICLE_ID = T.ARTICLE_ID
        GROUP BY 
            J.QUART
    )  AS C ON J.QUART = C.QUART
ORDER BY J.QUART;


--- Query: 150dd_MISC_SM_Year_Separ ---
SELECT Y.P_YEAR, Nz(C.Instagram,0) AS Instagram, Nz(C.LinkedIn,0) AS LinkedIn, Nz(C.Reddit,0) AS Reddit, Nz(C.TikTok,0) AS TikTok, Nz(C.Pinterest,0) AS Pinterest, Nz(C.Snapchat,0) AS Snapchat, Nz(C.Quora,0) AS Quora, Nz(C.Threads,0) AS Threads, Nz(C.WeChat,0) AS WeChat, Nz(C.Weibo,0) AS Weibo
FROM (SELECT DISTINCT P_YEAR FROM ARTICLES)  AS Y LEFT JOIN (SELECT A.P_YEAR, Sum(IIf(LCase(Nz(T.Extracted_URL,"")) Like "*instagram.com*" Or LCase(Nz(T.Extracted_URL,"")) Like "*instagr.am*",1,0)) AS Instagram, Sum(IIf(LCase(Nz(T.Extracted_URL,"")) Like "*linkedin.com*" Or LCase(Nz(T.Extracted_URL,"")) Like "*lnkd.in*",1,0)) AS LinkedIn, Sum(IIf(LCase(Nz(T.Extracted_URL,"")) Like "*reddit.com*" Or LCase(Nz(T.Extracted_URL,"")) Like "*redd.it*",1,0)) AS Reddit, Sum(IIf(LCase(Nz(T.Extracted_URL,"")) Like "*tiktok.com*" Or LCase(Nz(T.Extracted_URL,"")) Like "*vm.tiktok.com*" Or LCase(Nz(T.Extracted_URL,"")) Like "*vt.tiktok.com*",1,0)) AS TikTok, Sum(IIf(LCase(Nz(T.Extracted_URL,"")) Like "*pinterest.com*" Or LCase(Nz(T.Extracted_URL,"")) Like "*pin.it*",1,0)) AS Pinterest, Sum(IIf(LCase(Nz(T.Extracted_URL,"")) Like "*snapchat.com*" Or LCase(Nz(T.Extracted_URL,"")) Like "*story.snapchat.com*",1,0)) AS Snapchat, Sum(IIf(LCase(Nz(T.Extracted_URL,"")) Like "*quora.com*",1,0)) AS Quora, Sum(IIf(LCase(Nz(T.Extracted_URL,"")) Like "*threads.net*",1,0)) AS Threads, Sum(IIf(LCase(Nz(T.Extracted_URL,"")) Like "*wechat.com*" Or LCase(Nz(T.Extracted_URL,"")) Like "*weixin.qq.com*",1,0)) AS WeChat, Sum(IIf(LCase(Nz(T.Extracted_URL,"")) Like "*weibo.com*",1,0)) AS Weibo FROM ARTICLES AS A LEFT JOIN Total_Direct_Web_Links AS T ON A.ARTICLE_ID = T.ARTICLE_ID GROUP BY A.P_YEAR)  AS C ON Y.P_YEAR = C.P_YEAR
ORDER BY Y.P_YEAR;


--- Query: 200_TOTAL_URLS ---
SELECT R.REF_ID, R.ARTICLE_ID, Mid(R.REFERENCE_TEXT,IIf(InStr(1,R.REFERENCE_TEXT,"http://")>0,InStr(1,R.REFERENCE_TEXT,"http://"),IIf(InStr(1,R.REFERENCE_TEXT,"https://")>0,InStr(1,R.REFERENCE_TEXT,"https://"),InStr(1,R.REFERENCE_TEXT,"www."))),250) AS Extracted_URL
FROM [REFERENCES] AS R
WHERE (     R.REFERENCE_TEXT Like "*http://*"     OR R.REFERENCE_TEXT Like "*https://*"     OR R.REFERENCE_TEXT Like "*www.*" )
ORDER BY R.ARTICLE_ID, R.REF_ID;


--- Query: 200a_Total_Direct_Web_Links ---
SELECT R.REF_ID, R.ARTICLE_ID, Mid(R.REFERENCE_TEXT,IIf(InStr(1,R.REFERENCE_TEXT,"http://")>0,InStr(1,R.REFERENCE_TEXT,"http://"),IIf(InStr(1,R.REFERENCE_TEXT,"https://")>0,InStr(1,R.REFERENCE_TEXT,"https://"),InStr(1,R.REFERENCE_TEXT,"www."))),250) AS Extracted_URL
FROM [REFERENCES] AS R
WHERE (     R.REFERENCE_TEXT Like "*http://*"     OR R.REFERENCE_TEXT Like "*https://*"     OR R.REFERENCE_TEXT Like "*www.*" ) AND NOT (     R.REFERENCE_TEXT Like "*doi*org*" )
ORDER BY R.ARTICLE_ID, R.REF_ID;


--- Query: 201a_URLs_No_DOI_strings ---
SELECT R.REF_ID, R.ARTICLE_ID, Mid(
        R.REFERENCE_TEXT,
        IIf(
            InStr(1,R.REFERENCE_TEXT,"http://")>0,
            InStr(1,R.REFERENCE_TEXT,"http://"),
            IIf(
                InStr(1,R.REFERENCE_TEXT,"https://")>0,
                InStr(1,R.REFERENCE_TEXT,"https://"),
                InStr(1,R.REFERENCE_TEXT,"www.")
            )
        ),
        250
    ) AS Extracted_URL
FROM [REFERENCES] AS R
WHERE (
    R.REFERENCE_TEXT Like "*http://*"
    OR R.REFERENCE_TEXT Like "*https://*"
    OR R.REFERENCE_TEXT Like "*www.*"
)
AND NOT
(
    R.REFERENCE_TEXT Like "*doi:*"
    OR R.REFERENCE_TEXT Like "*doi :*"
)
ORDER BY R.ARTICLE_ID, R.REF_ID;


--- Query: 201b_URLs_No_DOI_URLs ---
SELECT R.REF_ID, R.ARTICLE_ID, Mid(R.REFERENCE_TEXT,IIf(InStr(1,R.REFERENCE_TEXT,"http://")>0,InStr(1,R.REFERENCE_TEXT,"http://"),IIf(InStr(1,R.REFERENCE_TEXT,"https://")>0,InStr(1,R.REFERENCE_TEXT,"https://"),InStr(1,R.REFERENCE_TEXT,"www."))),250) AS Extracted_URL
FROM [REFERENCES] AS R
WHERE (     R.REFERENCE_TEXT Like "*http://*"     OR R.REFERENCE_TEXT Like "*https://*"     OR R.REFERENCE_TEXT Like "*www.*" ) AND (     R.REFERENCE_TEXT Like "*doi:*"     OR R.REFERENCE_TEXT Like "*doi :*" ) AND NOT (     R.REFERENCE_TEXT Like "*doi*org*" )
ORDER BY R.ARTICLE_ID, R.REF_ID;


--- Query: 201c_DOI_URLS ---
SELECT R.REF_ID, R.ARTICLE_ID, Mid(R.REFERENCE_TEXT,IIf(InStr(1,R.REFERENCE_TEXT,"http://")>0,InStr(1,R.REFERENCE_TEXT,"http://"),IIf(InStr(1,R.REFERENCE_TEXT,"https://")>0,InStr(1,R.REFERENCE_TEXT,"https://"),InStr(1,R.REFERENCE_TEXT,"www."))),250) AS DOI_URL
FROM [REFERENCES] AS R
WHERE (     R.REFERENCE_TEXT Like "*http://*"     OR R.REFERENCE_TEXT Like "*https://*"     OR R.REFERENCE_TEXT Like "*www.*" ) AND (     R.REFERENCE_TEXT Like "*doi*org*" )
ORDER BY R.ARTICLE_ID, R.REF_ID;


--- Query: 201d_DOIs_Strings ---
SELECT R.REF_ID, R.ARTICLE_ID, Mid(R.REFERENCE_TEXT,IIf(InStr(1,R.REFERENCE_TEXT,"doi:")>0,InStr(1,R.REFERENCE_TEXT,"doi:"),InStr(1,R.REFERENCE_TEXT,"doi :")),250) AS DOI_STRING
FROM [REFERENCES] AS R
WHERE (     R.REFERENCE_TEXT Like "*doi:*"     OR R.REFERENCE_TEXT Like "*doi :*" )
ORDER BY R.ARTICLE_ID, R.REF_ID;


--- Query: 201e_Total_DOIs ---
SELECT R.REF_ID, R.ARTICLE_ID, Mid(R.REFERENCE_TEXT,IIf(InStr(1,R.REFERENCE_TEXT,"doi:")>0,InStr(1,R.REFERENCE_TEXT,"doi:"),IIf(InStr(1,R.REFERENCE_TEXT,"doi :")>0,InStr(1,R.REFERENCE_TEXT,"doi :"),IIf(InStr(1,R.REFERENCE_TEXT,"doi")>0,InStr(1,R.REFERENCE_TEXT,"doi"),IIf(InStr(1,R.REFERENCE_TEXT,"http://")>0,InStr(1,R.REFERENCE_TEXT,"http://"),IIf(InStr(1,R.REFERENCE_TEXT,"https://")>0,InStr(1,R.REFERENCE_TEXT,"https://"),InStr(1,R.REFERENCE_TEXT,"www.")))))),250) AS DOI_LOCATOR
FROM [REFERENCES] AS R
WHERE (     R.REFERENCE_TEXT Like "*doi*org*"     OR R.REFERENCE_TEXT Like "*doi:*"     OR R.REFERENCE_TEXT Like "*doi :*" )
ORDER BY R.ARTICLE_ID, R.REF_ID;


--- Query: 201f_Total_Web_Locators ---
SELECT R.REF_ID, R.ARTICLE_ID, Mid(R.REFERENCE_TEXT,IIf(InStr(1,R.REFERENCE_TEXT,"http://")>0,InStr(1,R.REFERENCE_TEXT,"http://"),IIf(InStr(1,R.REFERENCE_TEXT,"https://")>0,InStr(1,R.REFERENCE_TEXT,"https://"),IIf(InStr(1,R.REFERENCE_TEXT,"www.")>0,InStr(1,R.REFERENCE_TEXT,"www."),IIf(InStr(1,R.REFERENCE_TEXT,"doi:")>0,InStr(1,R.REFERENCE_TEXT,"doi:"),InStr(1,R.REFERENCE_TEXT,"doi :"))))),250) AS Extracted_Locator
FROM [REFERENCES] AS R
WHERE (     R.REFERENCE_TEXT Like "*http://*"     OR R.REFERENCE_TEXT Like "*https://*"     OR R.REFERENCE_TEXT Like "*www.*"     OR R.REFERENCE_TEXT Like "*doi*org*"     OR R.REFERENCE_TEXT Like "*doi:*"     OR R.REFERENCE_TEXT Like "*doi :*" )
ORDER BY R.ARTICLE_ID, R.REF_ID;


--- Query: 201g_DOI_strings_no_URL ---
SELECT R.REF_ID, R.ARTICLE_ID, Mid(R.REFERENCE_TEXT,IIf(InStr(1,R.REFERENCE_TEXT,"doi:")>0,InStr(1,R.REFERENCE_TEXT,"doi:"),IIf(InStr(1,R.REFERENCE_TEXT,"doi :")>0,InStr(1,R.REFERENCE_TEXT,"doi :"),InStr(1,R.REFERENCE_TEXT,"doi"))),200) AS DOI_Locator
FROM [REFERENCES] AS R
WHERE (     R.REFERENCE_TEXT Like "*doi*org*"     OR R.REFERENCE_TEXT Like "*doi:*"     OR R.REFERENCE_TEXT Like "*doi :*" ) AND NOT (     R.REFERENCE_TEXT Like "*http://*"     OR R.REFERENCE_TEXT Like "*https://*"     OR R.REFERENCE_TEXT Like "*www.*" )
ORDER BY R.ARTICLE_ID, R.REF_ID;


--- Query: 300_DOMAINS ---
SELECT Q.REF_ID, Q.ARTICLE_ID, Q.Extracted_URL, Q.URL_AfterPrefix, IIf(InStr(1,Q.URL_AfterPrefix,"/")>0,Left(Q.URL_AfterPrefix,InStr(1,Q.URL_AfterPrefix,"/")-1),Q.URL_AfterPrefix) AS [DOMAIN]
FROM qryURL_AfterPrefix AS Q
ORDER BY Q.ARTICLE_ID, Q.REF_ID;


--- Query: 301_domain_family_step1 ---
SELECT A.DOMAIN, A.Domain_Count, IIf(A.DOMAIN Like "*doi.org*","doi.org",IIf(A.DOMAIN Like "*jstor.org*","jstor.org",IIf(A.DOMAIN Like "*handle.net*","handle.net",IIf(A.DOMAIN Like "*arxiv.org*","arxiv.org",IIf(A.DOMAIN Like "*researchgate.net*","researchgate.net",IIf(A.DOMAIN Like "*github.com*","github.com",IIf(A.DOMAIN Like "*archive.org*","archive.org",IIf(A.DOMAIN Like "*ssrn.com*","ssrn.com",IIf(A.DOMAIN Like "*ncbi.nlm.nih.gov*","ncbi.nlm.nih.gov",IIf(A.DOMAIN Like "*aps.org*","aps.org",IIf(A.DOMAIN Like "*eric.ed.gov*","eric.ed.gov",A.DOMAIN))))))))))) AS DOMAIN_FAMILY
FROM 301_domain_frequency AS A;


--- Query: 301_domain_family_step2 ---
SELECT B.DOMAIN, B.Domain_Count, IIf(B.DOMAIN_FAMILY Like "*youtube.com*" Or B.DOMAIN_FAMILY Like "*youtu.be*","youtube.com",IIf(B.DOMAIN_FAMILY Like "*facebook.com*" Or B.DOMAIN_FAMILY Like "*fb.com*" Or B.DOMAIN_FAMILY Like "*fbcdn.net*" Or B.DOMAIN_FAMILY Like "*fbsbx.com*" Or B.DOMAIN_FAMILY Like "*facebook.net*" Or B.DOMAIN_FAMILY Like "*fb.gg*","facebook family",IIf(B.DOMAIN_FAMILY Like "*twitter.com*" Or B.DOMAIN_FAMILY Like "*x.com*","twitter/x",IIf(B.DOMAIN_FAMILY Like "*google.*","google",B.DOMAIN_FAMILY)))) AS DOMAIN_FAMILY2
FROM 301_domain_family_step1 AS B;


--- Query: 301_domain_family_step3 ---
SELECT C.DOMAIN, C.Domain_Count, IIf(C.DOMAIN_FAMILY2 Like "*sciencedirect.*" Or C.DOMAIN_FAMILY2 Like "*elsevier.*","elsevier",IIf(C.DOMAIN_FAMILY2 Like "*springer.*","springer",IIf(C.DOMAIN_FAMILY2 Like "*wiley.*","wiley",IIf(C.DOMAIN_FAMILY2 Like "*nature.*","nature",IIf(C.DOMAIN_FAMILY2 Like "*europa.eu*","europa.eu",C.DOMAIN_FAMILY2))))) AS DOMAIN_FAMILY3
FROM 301_domain_family_step2 AS C;


--- Query: 301_domain_frequency ---
SELECT T.DOMAIN, Count(*) AS Domain_Count
FROM tblURL_Domain_First AS T
WHERE T.DOMAIN Is Not Null
  AND T.DOMAIN <> ""
GROUP BY T.DOMAIN
ORDER BY Count(*) DESC;


--- Query: 301a_total_domains ---
SELECT Count(*) AS Total_Domain_Rows
FROM tblURL_Domain_First
WHERE DOMAIN Is Not Null
  AND DOMAIN <> "";


--- Query: 301b_domain_frequency_grouped ---
SELECT N.Domain_Group, Sum(N.Domain_Count) AS Grouped_Domain_Count
FROM (SELECT
        C.Domain_Count,
        Switch(
            C.Domain_Base In ("doi.org","dx.doi.org","doi.org","doi.","doiorg"), "doi.org",
            C.Domain_Base In ("jstor.org"), "jstor.org",
            C.Domain_Base In ("hdl.handle.net","handle.net"), "handle.net",
            C.Domain_Base In ("arxiv.org"), "arxiv.org",
            C.Domain_Base In ("eric.ed.gov","files.eric.ed.gov"), "eric.ed.gov",
            C.Domain_Base In ("researchgate.net"), "researchgate.net",
            C.Domain_Base In ("youtube.com","youtu.be","m.youtube.com"), "youtube.com",
            C.Domain_Base In ("archive.org","web.archive.org"), "archive.org",
            C.Domain_Base In ("ssrn.com","papers.ssrn.com"), "ssrn.com",
            C.Domain_Base In ("github.com","raw.githubusercontent.com","gist.github.com"), "github.com",
            C.Domain_Base In ("ncbi.nlm.nih.gov","pubmed.ncbi.nlm.nih.gov"), "ncbi.nlm.nih.gov",
            C.Domain_Base In ("aps.org","link.aps.org"), "aps.org",
            True, C.Domain_Base
        ) AS Domain_Group
    FROM
    (
        SELECT
            B.Domain_Count,
            IIf(
                Left(B.Domain_Clean,5)="www2.",
                Mid(B.Domain_Clean,6),
                IIf(
                    Left(B.Domain_Clean,4)="www.",
                    Mid(B.Domain_Clean,5),
                    B.Domain_Clean
                )
            ) AS Domain_Base
        FROM
        (
            SELECT
                A.Domain_Count,
                LCase(
                    Replace(
                        Replace(
                            Replace(
                                Replace(
                                    Replace(
                                        Replace(
                                            Replace(
                                                Replace(
                                                    Replace([DOMAIN], Chr(10), ""),
                                                    Chr(13), ""
                                                ),
                                                " ", ""
                                            ),
                                            "?", ""
                                        ),
                                        ";", ""
                                    ),
                                    ",", ""
                                ),
                                "(", ""
                            ),
                            ")", ""
                        ),
                        "]", ""
                    )
                ) AS Domain_Clean
            FROM [301_domain_frequency] AS A
        ) AS B
    ) AS C
)  AS N
GROUP BY N.Domain_Group
ORDER BY Sum(N.Domain_Count) DESC;


--- Query: 301c_domain_family_frequency ---
SELECT D.DOMAIN_FAMILY3 AS DOMAIN_FAMILY, Sum(D.Domain_Count) AS Family_Count
FROM 301_domain_family_step3 AS D
GROUP BY D.DOMAIN_FAMILY3
ORDER BY Sum(D.Domain_Count) DESC;


--- Query: 302a_DOIs_per_Journal ---
SELECT J.JOURNAL_ID, J.JOURNAL_NAME, Nz(C.DOI_Refs,0) AS DOI_Refs
FROM JOURNALS AS J LEFT JOIN (SELECT A.JOURNAL_ID, Count(*) AS DOI_Refs FROM ARTICLES AS A INNER JOIN [REFERENCES] AS R ON A.ARTICLE_ID = R.ARTICLE_ID WHERE (             R.REFERENCE_TEXT Like "*doi*org*"             OR R.REFERENCE_TEXT Like "*doi:*"             OR R.REFERENCE_TEXT Like "*doi :*"         ) GROUP BY A.JOURNAL_ID)  AS C ON J.JOURNAL_ID = C.JOURNAL_ID
ORDER BY J.JOURNAL_ID;


--- Query: 302b_DOIs_per_Subject ---
SELECT J.SUBJECT, Nz(C.DOI_Refs,0) AS DOI_Refs
FROM (SELECT SUBJECT FROM JOURNALS GROUP BY SUBJECT)  AS J LEFT JOIN (SELECT J2.SUBJECT, Count(*) AS DOI_Refs FROM (JOURNALS AS J2 INNER JOIN ARTICLES AS A ON J2.JOURNAL_ID = A.JOURNAL_ID) INNER JOIN [REFERENCES] AS R ON A.ARTICLE_ID = R.ARTICLE_ID WHERE (             R.REFERENCE_TEXT Like "*doi*org*"             OR R.REFERENCE_TEXT Like "*doi:*"             OR R.REFERENCE_TEXT Like "*doi :*"         ) GROUP BY J2.SUBJECT)  AS C ON J.SUBJECT = C.SUBJECT
ORDER BY J.SUBJECT;


--- Query: 302c_DOIs_per_Quart ---
SELECT J.QUART, Nz(C.DOI_Refs,0) AS DOI_Refs
FROM (SELECT QUART FROM JOURNALS GROUP BY QUART)  AS J LEFT JOIN (SELECT J2.QUART, Count(*) AS DOI_Refs FROM (JOURNALS AS J2 INNER JOIN ARTICLES AS A ON J2.JOURNAL_ID = A.JOURNAL_ID) INNER JOIN [REFERENCES] AS R ON A.ARTICLE_ID = R.ARTICLE_ID WHERE (             R.REFERENCE_TEXT Like "*doi*org*"             OR R.REFERENCE_TEXT Like "*doi:*"             OR R.REFERENCE_TEXT Like "*doi :*"         ) GROUP BY J2.QUART)  AS C ON J.QUART = C.QUART
ORDER BY J.QUART;


--- Query: 302d_DOIs_per_year ---
SELECT Y.P_YEAR, Nz(C.DOI_Refs,0) AS DOI_Refs
FROM (SELECT P_YEAR FROM ARTICLES GROUP BY P_YEAR)  AS Y LEFT JOIN (SELECT A.P_YEAR, Count(*) AS DOI_Refs FROM ARTICLES AS A INNER JOIN [REFERENCES] AS R ON A.ARTICLE_ID = R.ARTICLE_ID WHERE (             R.REFERENCE_TEXT Like "*doi*org*"             OR R.REFERENCE_TEXT Like "*doi:*"             OR R.REFERENCE_TEXT Like "*doi :*"         ) GROUP BY A.P_YEAR)  AS C ON Y.P_YEAR = C.P_YEAR
ORDER BY Y.P_YEAR;


--- Query: 302e_DOIs_per_article_by_Journal ---
SELECT J.JOURNAL_ID, J.JOURNAL_NAME, Nz(C.Articles,0) AS Articles, Nz(C.Total_DOI_Refs,0) AS Total_DOI_Refs, Round(Nz(C.Mean_DOI_Refs_Per_Article,0),2) AS Mean_DOI_Refs_Per_Article
FROM JOURNALS AS J LEFT JOIN (SELECT Q.JOURNAL_ID, Count(Q.ARTICLE_ID) AS Articles, Sum(Q.DOI_Refs) AS Total_DOI_Refs, Avg(Q.DOI_Refs) AS Mean_DOI_Refs_Per_Article FROM (SELECT A.JOURNAL_ID, A.ARTICLE_ID, Nz(Sum(IIf((R.REFERENCE_TEXT Like "*doi*org*" Or R.REFERENCE_TEXT Like "*doi:*" Or R.REFERENCE_TEXT Like "*doi :*"),1,0)),0) AS DOI_Refs FROM ARTICLES AS A LEFT JOIN [REFERENCES] AS R ON A.ARTICLE_ID = R.ARTICLE_ID GROUP BY A.JOURNAL_ID, A.ARTICLE_ID)  AS Q GROUP BY Q.JOURNAL_ID)  AS C ON J.JOURNAL_ID = C.JOURNAL_ID
ORDER BY J.JOURNAL_ID;


--- Query: 302f_DOIs_per_article_by_Subject ---
SELECT J.SUBJECT, Nz(C.Articles,0) AS Articles, Nz(C.Total_DOI_Refs,0) AS Total_DOI_Refs, Round(Nz(C.Mean_DOI_Refs_Per_Article,0),2) AS Mean_DOI_Refs_Per_Article
FROM (SELECT SUBJECT FROM JOURNALS GROUP BY SUBJECT)  AS J LEFT JOIN (SELECT Q.SUBJECT, Count(Q.ARTICLE_ID) AS Articles, Sum(Q.DOI_Refs) AS Total_DOI_Refs, Avg(Q.DOI_Refs) AS Mean_DOI_Refs_Per_Article FROM (SELECT J2.SUBJECT, A.ARTICLE_ID, Nz(Sum(IIf((R.REFERENCE_TEXT Like "*doi*org*" Or R.REFERENCE_TEXT Like "*doi:*" Or R.REFERENCE_TEXT Like "*doi :*"),1,0)),0) AS DOI_Refs FROM (JOURNALS AS J2 INNER JOIN ARTICLES AS A ON J2.JOURNAL_ID = A.JOURNAL_ID) LEFT JOIN [REFERENCES] AS R ON A.ARTICLE_ID = R.ARTICLE_ID GROUP BY J2.SUBJECT, A.ARTICLE_ID)  AS Q GROUP BY Q.SUBJECT)  AS C ON J.SUBJECT = C.SUBJECT
ORDER BY J.SUBJECT;


--- Query: 302g_DOIs_per_article_by_Quart ---
SELECT J.QUART, Nz(C.Articles,0) AS Articles, Nz(C.Total_DOI_Refs,0) AS Total_DOI_Refs, Round(Nz(C.Mean_DOI_Refs_Per_Article,0),2) AS Mean_DOI_Refs_Per_Article
FROM (SELECT QUART FROM JOURNALS GROUP BY QUART)  AS J LEFT JOIN (SELECT Q.QUART, Count(Q.ARTICLE_ID) AS Articles, Sum(Q.DOI_Refs) AS Total_DOI_Refs, Avg(Q.DOI_Refs) AS Mean_DOI_Refs_Per_Article FROM (SELECT J2.QUART, A.ARTICLE_ID, Nz(Sum(IIf((R.REFERENCE_TEXT Like "*doi*org*" Or R.REFERENCE_TEXT Like "*doi:*" Or R.REFERENCE_TEXT Like "*doi :*"),1,0)),0) AS DOI_Refs FROM (JOURNALS AS J2 INNER JOIN ARTICLES AS A ON J2.JOURNAL_ID = A.JOURNAL_ID) LEFT JOIN [REFERENCES] AS R ON A.ARTICLE_ID = R.ARTICLE_ID GROUP BY J2.QUART, A.ARTICLE_ID)  AS Q GROUP BY Q.QUART)  AS C ON J.QUART = C.QUART
ORDER BY J.QUART;


--- Query: 302h_DOIs_per_article_by_Year ---
SELECT Y.P_YEAR, Nz(C.Articles,0) AS Articles, Nz(C.Total_DOI_Refs,0) AS Total_DOI_Refs, Round(Nz(C.Mean_DOI_Refs_Per_Article,0),2) AS Mean_DOI_Refs_Per_Article
FROM (SELECT P_YEAR FROM ARTICLES GROUP BY P_YEAR)  AS Y LEFT JOIN (SELECT Q.P_YEAR, Count(Q.ARTICLE_ID) AS Articles, Sum(Q.DOI_Refs) AS Total_DOI_Refs, Avg(Q.DOI_Refs) AS Mean_DOI_Refs_Per_Article FROM (SELECT A.P_YEAR, A.ARTICLE_ID, Nz(Sum(IIf((R.REFERENCE_TEXT Like "*doi*org*" Or R.REFERENCE_TEXT Like "*doi:*" Or R.REFERENCE_TEXT Like "*doi :*"),1,0)),0) AS DOI_Refs FROM ARTICLES AS A LEFT JOIN [REFERENCES] AS R ON A.ARTICLE_ID = R.ARTICLE_ID GROUP BY A.P_YEAR, A.ARTICLE_ID)  AS Q GROUP BY Q.P_YEAR)  AS C ON Y.P_YEAR = C.P_YEAR
ORDER BY Y.P_YEAR;


--- Query: 303a_Direct_per_Journal ---
SELECT J.JOURNAL_ID, J.JOURNAL_NAME, Nz(W.Web_Locators,0) AS Web_Locators
FROM JOURNALS AS J LEFT JOIN (SELECT A.JOURNAL_ID, Count(*) AS Web_Locators FROM ARTICLES AS A INNER JOIN (SELECT R.ARTICLE_ID FROM [REFERENCES] AS R WHERE (                 R.REFERENCE_TEXT Like "*http://*"                 OR R.REFERENCE_TEXT Like "*https://*"                 OR R.REFERENCE_TEXT Like "*www.*"             )             AND NOT             (                 R.REFERENCE_TEXT Like "*doi*org*"             ))  AS W ON A.ARTICLE_ID = W.ARTICLE_ID GROUP BY A.JOURNAL_ID)  AS W ON J.JOURNAL_ID = W.JOURNAL_ID
ORDER BY J.JOURNAL_ID;


--- Query: 303b_Direct_per_Subject ---
SELECT JO.SUBJECT, Count(*) AS Web_Locators
FROM (JOURNALS AS JO INNER JOIN ARTICLES AS A ON JO.JOURNAL_ID = A.JOURNAL_ID) INNER JOIN (SELECT R.ARTICLE_ID FROM [REFERENCES] AS R WHERE (             R.REFERENCE_TEXT Like "*http://*"             OR R.REFERENCE_TEXT Like "*https://*"             OR R.REFERENCE_TEXT Like "*www.*"         )         AND NOT         (             R.REFERENCE_TEXT Like "*doi*org*"         ))  AS W ON A.ARTICLE_ID = W.ARTICLE_ID
GROUP BY JO.SUBJECT
ORDER BY JO.SUBJECT;


--- Query: 303c_Direct_per_Quart ---
SELECT JO.QUART, Count(*) AS Web_Locators
FROM (JOURNALS AS JO INNER JOIN ARTICLES AS A ON JO.JOURNAL_ID = A.JOURNAL_ID) INNER JOIN (SELECT R.ARTICLE_ID FROM [REFERENCES] AS R WHERE (             R.REFERENCE_TEXT Like "*http://*"             OR R.REFERENCE_TEXT Like "*https://*"             OR R.REFERENCE_TEXT Like "*www.*"         )         AND NOT         (             R.REFERENCE_TEXT Like "*doi*org*"         ))  AS W ON A.ARTICLE_ID = W.ARTICLE_ID
GROUP BY JO.QUART
ORDER BY JO.QUART;


--- Query: 303d_Direct_per_year ---
SELECT Y.P_YEAR, Nz(W.Web_Locators,0) AS Web_Locators
FROM (SELECT DISTINCT P_YEAR FROM ARTICLES)  AS Y LEFT JOIN (SELECT A.P_YEAR, Count(*) AS Web_Locators FROM ARTICLES AS A INNER JOIN (SELECT R.ARTICLE_ID FROM [REFERENCES] AS R WHERE (                 R.REFERENCE_TEXT Like "*http://*"                 OR R.REFERENCE_TEXT Like "*https://*"                 OR R.REFERENCE_TEXT Like "*www.*"             )             AND NOT             (                 R.REFERENCE_TEXT Like "*doi*org*"             ))  AS W ON A.ARTICLE_ID = W.ARTICLE_ID GROUP BY A.P_YEAR)  AS W ON Y.P_YEAR = W.P_YEAR
ORDER BY Y.P_YEAR;


--- Query: 303e_Direct_per_article_by_journal ---
SELECT J.JOURNAL_ID, J.JOURNAL_NAME, Nz(C.Articles,0) AS Articles, Nz(C.Total_Web_Links,0) AS Total_Web_Links, Round(Nz(C.Mean_Web_Links_Per_Article,0),2) AS Mean_Web_Links_Per_Article
FROM JOURNALS AS J LEFT JOIN (SELECT Q.JOURNAL_ID, Count(Q.ARTICLE_ID) AS Articles, Sum(Q.Web_Links) AS Total_Web_Links, Avg(Q.Web_Links) AS Mean_Web_Links_Per_Article FROM (SELECT A.JOURNAL_ID, A.ARTICLE_ID, Nz(Sum(IIf((R.REFERENCE_TEXT Like "*http://*" Or R.REFERENCE_TEXT Like "*https://*" Or R.REFERENCE_TEXT Like "*www.*") And Not (R.REFERENCE_TEXT Like "*doi*org*"),1,0)),0) AS Web_Links FROM ARTICLES AS A LEFT JOIN [REFERENCES] AS R ON A.ARTICLE_ID = R.ARTICLE_ID GROUP BY A.JOURNAL_ID, A.ARTICLE_ID)  AS Q GROUP BY Q.JOURNAL_ID)  AS C ON J.JOURNAL_ID = C.JOURNAL_ID
ORDER BY J.JOURNAL_ID;


--- Query: 303f_Direct_oer_Article_by_Subject ---
SELECT S.SUBJECT, Nz(C.Articles,0) AS Articles, Nz(C.Total_Web_Links,0) AS Total_Web_Links, Round(Nz(C.Mean_Web_Links_Per_Article,0),2) AS Mean_Web_Links_Per_Article
FROM (SELECT DISTINCT SUBJECT FROM JOURNALS)  AS S LEFT JOIN (SELECT Q.SUBJECT, Count(Q.ARTICLE_ID) AS Articles, Sum(Q.Web_Links) AS Total_Web_Links, Avg(Q.Web_Links) AS Mean_Web_Links_Per_Article FROM (SELECT J.SUBJECT, A.ARTICLE_ID, Nz(Sum(IIf((R.REFERENCE_TEXT Like "*http://*" Or R.REFERENCE_TEXT Like "*https://*" Or R.REFERENCE_TEXT Like "*www.*") And Not (R.REFERENCE_TEXT Like "*doi*org*"),1,0)),0) AS Web_Links FROM (JOURNALS AS J INNER JOIN ARTICLES AS A ON J.JOURNAL_ID = A.JOURNAL_ID) LEFT JOIN [REFERENCES] AS R ON A.ARTICLE_ID = R.ARTICLE_ID GROUP BY J.SUBJECT, A.ARTICLE_ID)  AS Q GROUP BY Q.SUBJECT)  AS C ON S.SUBJECT = C.SUBJECT
ORDER BY S.SUBJECT;


--- Query: 303g_Direct_per_article_by_Quart ---
SELECT Q0.QUART, Nz(C.Articles,0) AS Articles, Nz(C.Total_Web_Links,0) AS Total_Web_Links, Round(Nz(C.Mean_Web_Links_Per_Article,0),2) AS Mean_Web_Links_Per_Article
FROM (SELECT DISTINCT QUART FROM JOURNALS)  AS Q0 LEFT JOIN (SELECT Q.QUART, Count(Q.ARTICLE_ID) AS Articles, Sum(Q.Web_Links) AS Total_Web_Links, Avg(Q.Web_Links) AS Mean_Web_Links_Per_Article FROM (SELECT J.QUART, A.ARTICLE_ID, Nz(Sum(IIf((R.REFERENCE_TEXT Like "*http://*" Or R.REFERENCE_TEXT Like "*https://*" Or R.REFERENCE_TEXT Like "*www.*") And Not (R.REFERENCE_TEXT Like "*doi*org*"),1,0)),0) AS Web_Links FROM (JOURNALS AS J INNER JOIN ARTICLES AS A ON J.JOURNAL_ID = A.JOURNAL_ID) LEFT JOIN [REFERENCES] AS R ON A.ARTICLE_ID = R.ARTICLE_ID GROUP BY J.QUART, A.ARTICLE_ID)  AS Q GROUP BY Q.QUART)  AS C ON Q0.QUART = C.QUART
ORDER BY Q0.QUART;


--- Query: 303h_Direct_per_article_by_Year ---
SELECT Y0.P_YEAR, Nz(C.Articles,0) AS Articles, Nz(C.Total_Web_Links,0) AS Total_Web_Links, Round(Nz(C.Mean_Web_Links_Per_Article,0),2) AS Mean_Web_Links_Per_Article
FROM (SELECT DISTINCT P_YEAR FROM ARTICLES)  AS Y0 LEFT JOIN (SELECT Q.P_YEAR, Count(Q.ARTICLE_ID) AS Articles, Sum(Q.Web_Links) AS Total_Web_Links, Avg(Q.Web_Links) AS Mean_Web_Links_Per_Article FROM (SELECT A.P_YEAR, A.ARTICLE_ID, Nz(Sum(IIf((R.REFERENCE_TEXT Like "*http://*" Or R.REFERENCE_TEXT Like "*https://*" Or R.REFERENCE_TEXT Like "*www.*") And Not (R.REFERENCE_TEXT Like "*doi*org*"),1,0)),0) AS Web_Links FROM ARTICLES AS A LEFT JOIN [REFERENCES] AS R ON A.ARTICLE_ID = R.ARTICLE_ID GROUP BY A.P_YEAR, A.ARTICLE_ID)  AS Q GROUP BY Q.P_YEAR)  AS C ON Y0.P_YEAR = C.P_YEAR
ORDER BY Y0.P_YEAR;


--- Query: 304_1_TLD_Count_final ---
SELECT TLD, Count(*) AS TLD_Count
FROM tbl_TLD_Extract_Clean
WHERE TLD Is Not Null
    AND TLD <> ""
    AND Not (TLD Like "*[0-9]*" AND TLD Not Like "*[A-Za-z]*")
GROUP BY TLD
ORDER BY Count(*) DESC;


--- Query: 304_TLD_COUNT ---
SELECT TLD, Count(*) AS TLD_Count
FROM tbl_TLD_Extract_Clean
WHERE TLD Is Not Null
  AND TLD <> ""
GROUP BY TLD
ORDER BY Count(*) DESC;


--- Query: 304a_Rare_TLDs ---
SELECT TLD, Count(*) AS TLD_Count
FROM tbl_TLD_Extract_Clean
WHERE TLD Is Not Null
  AND TLD <> ""
GROUP BY TLD
HAVING Count(*) <= 5
ORDER BY Count(*) DESC , TLD;


--- Query: 304b_TLD_percents ---
SELECT TLD, Count(*) AS TLD_Count, Round(
        Count(*) * 100.0 /
        DCount("*","tbl_TLD_Extract_Clean_NoPorts"),
    2) AS TLD_Percent
FROM tbl_TLD_Extract_Clean_NoPorts
GROUP BY TLD
ORDER BY Count(*) DESC;


--- Query: 305_TDL_Country_Count ---
SELECT TLD, Count(*) AS Country_Count
FROM Q_TLD_Extract_NoDOI
WHERE TLD In ("uk","ca","de","ru","br","au","id","nl","ua","fr","es","ch","it","sk","tr","cn","jp","se","fi","in","mx","us","be","za","nz","ie","at","ir","no","my","pl","dk","ng","il","hr","hk","cl","sg","cz","ph","pt","ar","kz","lv","ge","kr","tw","ke","vn","ro","pe","gr","pk","ug")
GROUP BY TLD
ORDER BY Count(*) DESC;


--- Query: 305b_TLD_Continents ---
SELECT Switch(TLD In ("uk","de","fr","es","it","nl","be","ch","sk","se","fi","dk","pl","cz","ro","lv","ge","gr","ie","at","pt","ua","hr","ru"),"Europe",TLD In ("us","ca","mx"),"North America",TLD In ("br","ar","pe","cl"),"South America",TLD In ("cn","jp","kr","in","ir","tr","sg","hk","tw","vn","id","pk","my","il","ph","kz"),"Asia",TLD In ("za","ng","ke","ug"),"Africa",TLD In ("au","nz"),"Oceania",True,"Other") AS Continent, Count(*) AS URL_Count
FROM Q_TLD_Extract_NoDOI
GROUP BY Switch(TLD In ("uk","de","fr","es","it","nl","be","ch","sk","se","fi","dk","pl","cz","ro","lv","ge","gr","ie","at","pt","ua","hr","ru"),"Europe",TLD In ("us","ca","mx"),"North America",TLD In ("br","ar","pe","cl"),"South America",TLD In ("cn","jp","kr","in","ir","tr","sg","hk","tw","vn","id","pk","my","il","ph","kz"),"Asia",TLD In ("za","ng","ke","ug"),"Africa",TLD In ("au","nz"),"Oceania",True,"Other")
ORDER BY Count(*) DESC;


--- Query: 306_Extension_count ---
SELECT File_Extension, Count(*) AS Extension_Count
FROM Q_File_Extension_Extract
GROUP BY File_Extension
ORDER BY Count(*) DESC;


--- Query: 306a_Extension_Categories ---
SELECT IIf(File_Extension In ("pdf","doc","docx","xls","xlsx","ppt","pptx","txt"),"Document files",IIf(File_Extension In ("html","htm","php","asp","aspx","jsp","cfm"),"Webpage files",IIf(File_Extension In ("csv","json","xml","zip"),"Data / archive files","Other"))) AS File_Type, Count(*) AS File_Count
FROM Q_File_Extension_Extract
GROUP BY IIf(File_Extension In ("pdf","doc","docx","xls","xlsx","ppt","pptx","txt"),"Document files",IIf(File_Extension In ("html","htm","php","asp","aspx","jsp","cfm"),"Webpage files",IIf(File_Extension In ("csv","json","xml","zip"),"Data / archive files","Other")))
ORDER BY Count(*) DESC;


--- Query: 400_WIKIPEDIA_WIKIMEDIA ---
SELECT *
FROM TOTAL_REFS
WHERE LCase(Extracted_URL) LIKE "*wikipedia.org*"
OR LCase(Extracted_URL) LIKE "*wikimedia.org*";


--- Query: 400a_WIKIPEDIA_per_Journal ---
SELECT J.JOURNAL_ID, J.JOURNAL_NAME, Nz(C.Wiki_URL_Occurrences,0) AS Wiki_URL_Occurrences
FROM JOURNALS AS J LEFT JOIN (SELECT 
            A.JOURNAL_ID,
            Count(*) AS Wiki_URL_Occurrences
        FROM 
            TOTAL_REFS AS T
            INNER JOIN ARTICLES AS A
                ON T.ARTICLE_ID = A.ARTICLE_ID
        WHERE 
            LCase(T.Extracted_URL) LIKE "*wikipedia.org*"
            OR LCase(T.Extracted_URL) LIKE "*wikimedia.org*"
        GROUP BY 
            A.JOURNAL_ID
    )  AS C ON J.JOURNAL_ID = C.JOURNAL_ID
ORDER BY J.JOURNAL_ID;


--- Query: 400b_WIKIPEDIA_per_Subject ---
SELECT J.SUBJECT, Nz(C.Wiki_URL_Occurrences,0) AS Wiki_URL_Occurrences
FROM (SELECT DISTINCT SUBJECT
        FROM JOURNALS
    )  AS J LEFT JOIN (SELECT 
            J2.SUBJECT,
            Count(*) AS Wiki_URL_Occurrences
        FROM 
            (TOTAL_REFS AS T
            INNER JOIN ARTICLES AS A
                ON T.ARTICLE_ID = A.ARTICLE_ID)
            INNER JOIN JOURNALS AS J2
                ON A.JOURNAL_ID = J2.JOURNAL_ID
        WHERE 
            LCase(T.Extracted_URL) LIKE "*wikipedia.org*"
            OR LCase(T.Extracted_URL) LIKE "*wikimedia.org*"
        GROUP BY 
            J2.SUBJECT
    )  AS C ON J.SUBJECT = C.SUBJECT
ORDER BY J.SUBJECT;


--- Query: 400c_WIKIPEDIA_per_Quart ---
SELECT J.QUART, Nz(C.Wiki_URL_Occurrences,0) AS Wiki_URL_Occurrences
FROM (SELECT DISTINCT QUART
        FROM JOURNALS
    )  AS J LEFT JOIN (SELECT 
            J2.QUART,
            Count(*) AS Wiki_URL_Occurrences
        FROM 
            (TOTAL_REFS AS T
            INNER JOIN ARTICLES AS A
                ON T.ARTICLE_ID = A.ARTICLE_ID)
            INNER JOIN JOURNALS AS J2
                ON A.JOURNAL_ID = J2.JOURNAL_ID
        WHERE 
            LCase(T.Extracted_URL) LIKE "*wikipedia.org*"
            OR LCase(T.Extracted_URL) LIKE "*wikimedia.org*"
        GROUP BY 
            J2.QUART
    )  AS C ON J.QUART = C.QUART
ORDER BY J.QUART;


--- Query: 400d_WIKIPEDIA_per_Year ---
SELECT Y.P_YEAR, Nz(C.Wiki_URL_Occurrences,0) AS Wiki_URL_Occurrences
FROM (SELECT DISTINCT P_YEAR
        FROM ARTICLES
    )  AS Y LEFT JOIN (SELECT 
            A.P_YEAR,
            Count(*) AS Wiki_URL_Occurrences
        FROM 
            TOTAL_REFS AS T
            INNER JOIN ARTICLES AS A
                ON T.ARTICLE_ID = A.ARTICLE_ID
        WHERE 
            LCase(T.Extracted_URL) LIKE "*wikipedia.org*"
            OR LCase(T.Extracted_URL) LIKE "*wikimedia.org*"
        GROUP BY 
            A.P_YEAR
    )  AS C ON Y.P_YEAR = C.P_YEAR
ORDER BY Y.P_YEAR;


--- Query: 401_WIKIS ---
SELECT T.REF_ID, T.ARTICLE_ID, T.Extracted_URL
FROM Total_Direct_Web_Links AS T
WHERE (
        LCase(Replace(Replace(T.Extracted_URL, Chr(10), ""), Chr(13), "")) LIKE "*wiki*"
        OR LCase(T.Extracted_URL) LIKE "*fandom.com*"
        OR LCase(T.Extracted_URL) LIKE "*wikidot.com*"
        OR LCase(T.Extracted_URL) LIKE "*pbworks.com*"
        OR LCase(T.Extracted_URL) LIKE "*wikispaces*"
    )
    AND NOT (
        LCase(Replace(Replace(T.Extracted_URL, Chr(10), ""), Chr(13), "")) LIKE "*wikipedia.org*"
        OR LCase(Replace(Replace(T.Extracted_URL, Chr(10), ""), Chr(13), "")) LIKE "*wikimedia.org*" OR LCase(Replace(Replace(T.Extracted_URL, Chr(10), ""), Chr(13), "")) LIKE "*wiki*media.org*"
);


--- Query: 401a_WIKIS_by_Journal ---
SELECT J.JOURNAL_ID, J.JOURNAL_NAME, Nz(C.Wiki_Occurrences,0) AS Wiki_Occurrences
FROM JOURNALS AS J LEFT JOIN (SELECT 
            A.JOURNAL_ID,
            Count(*) AS Wiki_Occurrences
        FROM 
            Total_Direct_Web_Links AS T
            INNER JOIN ARTICLES AS A
                ON T.ARTICLE_ID = A.ARTICLE_ID
        WHERE 
            (
                LCase(Replace(Replace(T.Extracted_URL, Chr(10), ""), Chr(13), "")) LIKE "*wiki*"
                OR LCase(T.Extracted_URL) LIKE "*fandom.com*"
                OR LCase(T.Extracted_URL) LIKE "*wikidot.com*"
                OR LCase(T.Extracted_URL) LIKE "*pbworks.com*"
                OR LCase(T.Extracted_URL) LIKE "*wikispaces*"
            )
            AND NOT (
                LCase(Replace(Replace(T.Extracted_URL, Chr(10), ""), Chr(13), "")) LIKE "*wikipedia.org*"
                OR LCase(Replace(Replace(T.Extracted_URL, Chr(10), ""), Chr(13), "")) LIKE "*wikimedia.org*"
                OR LCase(Replace(Replace(T.Extracted_URL, Chr(10), ""), Chr(13), "")) LIKE "*wiki*media.org*"
            )
        GROUP BY 
            A.JOURNAL_ID
    )  AS C ON J.JOURNAL_ID = C.JOURNAL_ID
ORDER BY J.JOURNAL_ID;


--- Query: 401b_WIKIS_by_Subject ---
SELECT J.SUBJECT, Nz(C.Wiki_Occurrences,0) AS Wiki_Occurrences
FROM (SELECT DISTINCT SUBJECT
        FROM JOURNALS
    )  AS J LEFT JOIN (SELECT 
            J2.SUBJECT,
            Count(*) AS Wiki_Occurrences
        FROM 
            (Total_Direct_Web_Links AS T
            INNER JOIN ARTICLES AS A
                ON T.ARTICLE_ID = A.ARTICLE_ID)
            INNER JOIN JOURNALS AS J2
                ON A.JOURNAL_ID = J2.JOURNAL_ID
        WHERE 
            (
                LCase(Replace(Replace(T.Extracted_URL, Chr(10), ""), Chr(13), "")) LIKE "*wiki*"
                OR LCase(T.Extracted_URL) LIKE "*fandom.com*"
                OR LCase(T.Extracted_URL) LIKE "*wikidot.com*"
                OR LCase(T.Extracted_URL) LIKE "*pbworks.com*"
                OR LCase(T.Extracted_URL) LIKE "*wikispaces*"
            )
            AND NOT (
                LCase(Replace(Replace(T.Extracted_URL, Chr(10), ""), Chr(13), "")) LIKE "*wikipedia.org*"
                OR LCase(Replace(Replace(T.Extracted_URL, Chr(10), ""), Chr(13), "")) LIKE "*wikimedia.org*"
                OR LCase(Replace(Replace(T.Extracted_URL, Chr(10), ""), Chr(13), "")) LIKE "*wiki*media.org*"
            )
        GROUP BY 
            J2.SUBJECT
    )  AS C ON J.SUBJECT = C.SUBJECT
ORDER BY J.SUBJECT;


--- Query: 401c_WIKIS_by_Quart ---
SELECT J.QUART, Nz(C.Wiki_Occurrences,0) AS Wiki_Occurrences
FROM (SELECT DISTINCT QUART
        FROM JOURNALS
    )  AS J LEFT JOIN (SELECT 
            J2.QUART,
            Count(*) AS Wiki_Occurrences
        FROM 
            (Total_Direct_Web_Links AS T
            INNER JOIN ARTICLES AS A
                ON T.ARTICLE_ID = A.ARTICLE_ID)
            INNER JOIN JOURNALS AS J2
                ON A.JOURNAL_ID = J2.JOURNAL_ID
        WHERE 
            (
                LCase(Replace(Replace(T.Extracted_URL, Chr(10), ""), Chr(13), "")) LIKE "*wiki*"
                OR LCase(T.Extracted_URL) LIKE "*fandom.com*"
                OR LCase(T.Extracted_URL) LIKE "*wikidot.com*"
                OR LCase(T.Extracted_URL) LIKE "*pbworks.com*"
                OR LCase(T.Extracted_URL) LIKE "*wikispaces*"
            )
            AND NOT (
                LCase(Replace(Replace(T.Extracted_URL, Chr(10), ""), Chr(13), "")) LIKE "*wikipedia.org*"
                OR LCase(Replace(Replace(T.Extracted_URL, Chr(10), ""), Chr(13), "")) LIKE "*wikimedia.org*"
                OR LCase(Replace(Replace(T.Extracted_URL, Chr(10), ""), Chr(13), "")) LIKE "*wiki*media.org*"
            )
        GROUP BY 
            J2.QUART
    )  AS C ON J.QUART = C.QUART
ORDER BY J.QUART;


--- Query: 401d_WIKIS_by_Year ---
SELECT Y.P_YEAR, Nz(C.Wiki_Occurrences,0) AS Wiki_Occurrences
FROM (SELECT DISTINCT P_YEAR
        FROM ARTICLES
    )  AS Y LEFT JOIN (SELECT 
            A.P_YEAR,
            Count(*) AS Wiki_Occurrences
        FROM 
            Total_Direct_Web_Links AS T
            INNER JOIN ARTICLES AS A
                ON T.ARTICLE_ID = A.ARTICLE_ID
        WHERE 
            (
                LCase(Replace(Replace(T.Extracted_URL, Chr(10), ""), Chr(13), "")) LIKE "*wiki*"
                OR LCase(T.Extracted_URL) LIKE "*fandom.com*"
                OR LCase(T.Extracted_URL) LIKE "*wikidot.com*"
                OR LCase(T.Extracted_URL) LIKE "*pbworks.com*"
                OR LCase(T.Extracted_URL) LIKE "*wikispaces*"
            )
            AND NOT (
                LCase(Replace(Replace(T.Extracted_URL, Chr(10), ""), Chr(13), "")) LIKE "*wikipedia.org*"
                OR LCase(Replace(Replace(T.Extracted_URL, Chr(10), ""), Chr(13), "")) LIKE "*wikimedia.org*"
                OR LCase(Replace(Replace(T.Extracted_URL, Chr(10), ""), Chr(13), "")) LIKE "*wiki*media.org*"
            )
        GROUP BY 
            A.P_YEAR
    )  AS C ON Y.P_YEAR = C.P_YEAR
ORDER BY Y.P_YEAR;


--- Query: 402_Blogs ---
SELECT T2.REF_ID, T2.ARTICLE_ID, T2.Clean_URL AS Extracted_URL
FROM (SELECT REF_ID, ARTICLE_ID, Replace(Replace(Extracted_URL,Chr(10),""),Chr(13),"") AS Clean_URL FROM Total_Direct_Web_Links)  AS T2
WHERE (         T2.Clean_URL LIKE "*blog*"         OR T2.Clean_URL LIKE "*blogger.com*"         OR T2.Clean_URL LIKE "*blogspot.*"         OR T2.Clean_URL LIKE "*wordpress.*"         OR T2.Clean_URL LIKE "*tumblr.com*"         OR T2.Clean_URL LIKE "*medium.com*"         OR T2.Clean_URL LIKE "*substack.com*"         OR T2.Clean_URL LIKE "*ghost.*"         OR T2.Clean_URL LIKE "*wixsite.com*"         OR T2.Clean_URL LIKE "*wix.com*"         OR T2.Clean_URL LIKE "*weebly.com*"         OR T2.Clean_URL LIKE "*squarespace.com*"         OR T2.Clean_URL LIKE "*beehiiv.com*"     )     AND T2.Clean_URL NOT LIKE "*wikipedia.org*"     AND T2.Clean_URL NOT LIKE "*wikimedia.org*"
ORDER BY T2.REF_ID;


--- Query: 402a_Blogs_by_Journal ---
SELECT J.JOURNAL_ID, J.JOURNAL_NAME, Nz(C.Blog_URL_Occurrences,0) AS Blog_URL_Occurrences
FROM JOURNALS AS J LEFT JOIN (SELECT 
            A.JOURNAL_ID,
            Count(*) AS Blog_URL_Occurrences
        FROM 
            Total_Direct_Web_Links AS T
            INNER JOIN ARTICLES AS A
                ON T.ARTICLE_ID = A.ARTICLE_ID
        WHERE 
            (
                Replace(Replace(T.Extracted_URL, Chr(10), ""), Chr(13), "") LIKE "*blog*"
                OR Replace(Replace(T.Extracted_URL, Chr(10), ""), Chr(13), "") LIKE "*blogger.com*"
                OR Replace(Replace(T.Extracted_URL, Chr(10), ""), Chr(13), "") LIKE "*blogspot.*"
                OR Replace(Replace(T.Extracted_URL, Chr(10), ""), Chr(13), "") LIKE "*wordpress.*"
                OR Replace(Replace(T.Extracted_URL, Chr(10), ""), Chr(13), "") LIKE "*tumblr.com*"
                OR Replace(Replace(T.Extracted_URL, Chr(10), ""), Chr(13), "") LIKE "*medium.com*"
                OR Replace(Replace(T.Extracted_URL, Chr(10), ""), Chr(13), "") LIKE "*substack.com*"
                OR Replace(Replace(T.Extracted_URL, Chr(10), ""), Chr(13), "") LIKE "*ghost.*"
                OR Replace(Replace(T.Extracted_URL, Chr(10), ""), Chr(13), "") LIKE "*wixsite.com*"
                OR Replace(Replace(T.Extracted_URL, Chr(10), ""), Chr(13), "") LIKE "*wix.com*"
                OR Replace(Replace(T.Extracted_URL, Chr(10), ""), Chr(13), "") LIKE "*weebly.com*"
                OR Replace(Replace(T.Extracted_URL, Chr(10), ""), Chr(13), "") LIKE "*squarespace.com*"
                OR Replace(Replace(T.Extracted_URL, Chr(10), ""), Chr(13), "") LIKE "*beehiiv.com*"
            )
            AND Replace(Replace(T.Extracted_URL, Chr(10), ""), Chr(13), "") NOT LIKE "*wikipedia.org*"
            AND Replace(Replace(T.Extracted_URL, Chr(10), ""), Chr(13), "") NOT LIKE "*wikimedia.org*"
        GROUP BY 
            A.JOURNAL_ID
    )  AS C ON J.JOURNAL_ID = C.JOURNAL_ID
ORDER BY J.JOURNAL_ID;


--- Query: 402b_Blogs_per_Subject ---
SELECT J.SUBJECT, Nz(C.Blog_URL_Occurrences,0) AS Blog_URL_Occurrences
FROM (SELECT DISTINCT SUBJECT
        FROM JOURNALS
    )  AS J LEFT JOIN (SELECT 
            J2.SUBJECT,
            Count(*) AS Blog_URL_Occurrences
        FROM 
            (Total_Direct_Web_Links AS T
            INNER JOIN ARTICLES AS A
                ON T.ARTICLE_ID = A.ARTICLE_ID)
            INNER JOIN JOURNALS AS J2
                ON A.JOURNAL_ID = J2.JOURNAL_ID
        WHERE 
            (
                Replace(Replace(T.Extracted_URL, Chr(10), ""), Chr(13), "") LIKE "*blog*"
                OR Replace(Replace(T.Extracted_URL, Chr(10), ""), Chr(13), "") LIKE "*blogger.com*"
                OR Replace(Replace(T.Extracted_URL, Chr(10), ""), Chr(13), "") LIKE "*blogspot.*"
                OR Replace(Replace(T.Extracted_URL, Chr(10), ""), Chr(13), "") LIKE "*wordpress.*"
                OR Replace(Replace(T.Extracted_URL, Chr(10), ""), Chr(13), "") LIKE "*tumblr.com*"
                OR Replace(Replace(T.Extracted_URL, Chr(10), ""), Chr(13), "") LIKE "*medium.com*"
                OR Replace(Replace(T.Extracted_URL, Chr(10), ""), Chr(13), "") LIKE "*substack.com*"
                OR Replace(Replace(T.Extracted_URL, Chr(10), ""), Chr(13), "") LIKE "*ghost.*"
                OR Replace(Replace(T.Extracted_URL, Chr(10), ""), Chr(13), "") LIKE "*wixsite.com*"
                OR Replace(Replace(T.Extracted_URL, Chr(10), ""), Chr(13), "") LIKE "*wix.com*"
                OR Replace(Replace(T.Extracted_URL, Chr(10), ""), Chr(13), "") LIKE "*weebly.com*"
                OR Replace(Replace(T.Extracted_URL, Chr(10), ""), Chr(13), "") LIKE "*squarespace.com*"
                OR Replace(Replace(T.Extracted_URL, Chr(10), ""), Chr(13), "") LIKE "*beehiiv.com*"
            )
            AND Replace(Replace(T.Extracted_URL, Chr(10), ""), Chr(13), "") NOT LIKE "*wikipedia.org*"
            AND Replace(Replace(T.Extracted_URL, Chr(10), ""), Chr(13), "") NOT LIKE "*wikimedia.org*"
        GROUP BY 
            J2.SUBJECT
    )  AS C ON J.SUBJECT = C.SUBJECT
ORDER BY J.SUBJECT;


--- Query: 402c_Blogs_by_quart ---
SELECT J.QUART, Nz(C.Blog_URL_Occurrences,0) AS Blog_URL_Occurrences
FROM (SELECT DISTINCT QUART
        FROM JOURNALS
    )  AS J LEFT JOIN (SELECT 
            J2.QUART,
            Count(*) AS Blog_URL_Occurrences
        FROM 
            (Total_Direct_Web_Links AS T
            INNER JOIN ARTICLES AS A
                ON T.ARTICLE_ID = A.ARTICLE_ID)
            INNER JOIN JOURNALS AS J2
                ON A.JOURNAL_ID = J2.JOURNAL_ID
        WHERE 
            (
                Replace(Replace(T.Extracted_URL, Chr(10), ""), Chr(13), "") LIKE "*blog*"
                OR Replace(Replace(T.Extracted_URL, Chr(10), ""), Chr(13), "") LIKE "*blogger.com*"
                OR Replace(Replace(T.Extracted_URL, Chr(10), ""), Chr(13), "") LIKE "*blogspot.*"
                OR Replace(Replace(T.Extracted_URL, Chr(10), ""), Chr(13), "") LIKE "*wordpress.*"
                OR Replace(Replace(T.Extracted_URL, Chr(10), ""), Chr(13), "") LIKE "*tumblr.com*"
                OR Replace(Replace(T.Extracted_URL, Chr(10), ""), Chr(13), "") LIKE "*medium.com*"
                OR Replace(Replace(T.Extracted_URL, Chr(10), ""), Chr(13), "") LIKE "*substack.com*"
                OR Replace(Replace(T.Extracted_URL, Chr(10), ""), Chr(13), "") LIKE "*ghost.*"
                OR Replace(Replace(T.Extracted_URL, Chr(10), ""), Chr(13), "") LIKE "*wixsite.com*"
                OR Replace(Replace(T.Extracted_URL, Chr(10), ""), Chr(13), "") LIKE "*wix.com*"
                OR Replace(Replace(T.Extracted_URL, Chr(10), ""), Chr(13), "") LIKE "*weebly.com*"
                OR Replace(Replace(T.Extracted_URL, Chr(10), ""), Chr(13), "") LIKE "*squarespace.com*"
                OR Replace(Replace(T.Extracted_URL, Chr(10), ""), Chr(13), "") LIKE "*beehiiv.com*"
            )
            AND Replace(Replace(T.Extracted_URL, Chr(10), ""), Chr(13), "") NOT LIKE "*wikipedia.org*"
            AND Replace(Replace(T.Extracted_URL, Chr(10), ""), Chr(13), "") NOT LIKE "*wikimedia.org*"
        GROUP BY 
            J2.QUART
    )  AS C ON J.QUART = C.QUART
ORDER BY J.QUART;


--- Query: 402d_Blogs_by_year ---
SELECT Y.P_YEAR, Nz(C.Blog_URL_Occurrences,0) AS Blog_URL_Occurrences
FROM (SELECT DISTINCT P_YEAR
        FROM ARTICLES
    )  AS Y LEFT JOIN (SELECT 
            A.P_YEAR,
            Count(*) AS Blog_URL_Occurrences
        FROM 
            Total_Direct_Web_Links AS T
            INNER JOIN ARTICLES AS A
                ON T.ARTICLE_ID = A.ARTICLE_ID
        WHERE 
            (
                Replace(Replace(T.Extracted_URL, Chr(10), ""), Chr(13), "") LIKE "*blog*"
                OR Replace(Replace(T.Extracted_URL, Chr(10), ""), Chr(13), "") LIKE "*blogger.com*"
                OR Replace(Replace(T.Extracted_URL, Chr(10), ""), Chr(13), "") LIKE "*blogspot.*"
                OR Replace(Replace(T.Extracted_URL, Chr(10), ""), Chr(13), "") LIKE "*wordpress.*"
                OR Replace(Replace(T.Extracted_URL, Chr(10), ""), Chr(13), "") LIKE "*tumblr.com*"
                OR Replace(Replace(T.Extracted_URL, Chr(10), ""), Chr(13), "") LIKE "*medium.com*"
                OR Replace(Replace(T.Extracted_URL, Chr(10), ""), Chr(13), "") LIKE "*substack.com*"
                OR Replace(Replace(T.Extracted_URL, Chr(10), ""), Chr(13), "") LIKE "*ghost.*"
                OR Replace(Replace(T.Extracted_URL, Chr(10), ""), Chr(13), "") LIKE "*wixsite.com*"
                OR Replace(Replace(T.Extracted_URL, Chr(10), ""), Chr(13), "") LIKE "*wix.com*"
                OR Replace(Replace(T.Extracted_URL, Chr(10), ""), Chr(13), "") LIKE "*weebly.com*"
                OR Replace(Replace(T.Extracted_URL, Chr(10), ""), Chr(13), "") LIKE "*squarespace.com*"
                OR Replace(Replace(T.Extracted_URL, Chr(10), ""), Chr(13), "") LIKE "*beehiiv.com*"
            )
            AND Replace(Replace(T.Extracted_URL, Chr(10), ""), Chr(13), "") NOT LIKE "*wikipedia.org*"
            AND Replace(Replace(T.Extracted_URL, Chr(10), ""), Chr(13), "") NOT LIKE "*wikimedia.org*"
        GROUP BY 
            A.P_YEAR
    )  AS C ON Y.P_YEAR = C.P_YEAR
ORDER BY Y.P_YEAR;


--- Query: 500_first_URL_extraction ---
SELECT Q.REF_ID, Q.ARTICLE_ID, Mid(Q.REFERENCE_TEXT,Q.URL_Start,250) AS FIRST_URL, Q.REFERENCE_TEXT
FROM Q_Mixed_FirstURL AS Q
WHERE Q.URL_Start > 0;


--- Query: Q_Article_Journal ---
SELECT J.JOURNAL_ID, Count(B.ARTICLE_ID) AS Articles, Sum(B.WebLocators) AS Total_WebLocators, Avg(B.WebLocators) AS Avg_WebLocators, Sum(B.DirectWebLinks) AS Total_DWL, Avg(B.DirectWebLinks) AS Avg_DWL, Sum(B.DOIRefs) AS Total_DOI, Avg(B.DOIRefs) AS Avg_DOI
FROM Q_Article_Level_Base AS B INNER JOIN JOURNALS AS J ON B.JOURNAL_ID = J.JOURNAL_ID
GROUP BY J.JOURNAL_ID
ORDER BY J.JOURNAL_ID;


--- Query: Q_Article_Level_Base ---
SELECT A.ARTICLE_ID, A.JOURNAL_ID, A.P_YEAR, Count(R.REF_ID) AS TotalRefs, Sum(IIf(
        R.REFERENCE_TEXT Like "*http://*"
        OR R.REFERENCE_TEXT Like "*https://*"
        OR R.REFERENCE_TEXT Like "*www.*"
        OR R.REFERENCE_TEXT Like "*doi*org*"
        OR R.REFERENCE_TEXT Like "*doi:*"
        OR R.REFERENCE_TEXT Like "*doi :*",
        1, 0)) AS WebLocators, Sum(IIf(
        (R.REFERENCE_TEXT Like "*http://*"
        OR R.REFERENCE_TEXT Like "*https://*"
        OR R.REFERENCE_TEXT Like "*www.*")
        AND NOT (R.REFERENCE_TEXT Like "*doi*org*"),
        1, 0)) AS DirectWebLinks, Sum(IIf(
        R.REFERENCE_TEXT Like "*doi*org*"
        OR R.REFERENCE_TEXT Like "*doi:*"
        OR R.REFERENCE_TEXT Like "*doi :*",
        1, 0)) AS DOIRefs
FROM ARTICLES AS A LEFT JOIN [REFERENCES] AS R ON A.ARTICLE_ID = R.ARTICLE_ID
GROUP BY A.ARTICLE_ID, A.JOURNAL_ID, A.P_YEAR;


--- Query: Q_Article_Quartile ---
SELECT J.QUART, Count(B.ARTICLE_ID) AS Articles, Sum(B.WebLocators) AS Total_WebLocators, Avg(B.WebLocators) AS Avg_WebLocators, Sum(B.DirectWebLinks) AS Total_DWL, Avg(B.DirectWebLinks) AS Avg_DWL, Sum(B.DOIRefs) AS Total_DOI, Avg(B.DOIRefs) AS Avg_DOI
FROM Q_Article_Level_Base AS B INNER JOIN JOURNALS AS J ON B.JOURNAL_ID = J.JOURNAL_ID
GROUP BY J.QUART
ORDER BY J.QUART;


--- Query: Q_Article_Ref_Bins ---
SELECT Q.ARTICLE_ID, Q.Reference_Count, IIf(Q.Reference_Count=0,"0",IIf(Q.Reference_Count Between 1 And 9,"01-09",IIf(Q.Reference_Count Between 10 And 19,"10-19",IIf(Q.Reference_Count Between 20 And 29,"20-29",IIf(Q.Reference_Count Between 30 And 39,"30-39",IIf(Q.Reference_Count Between 40 And 49,"40-49",IIf(Q.Reference_Count Between 50 And 59,"50-59",IIf(Q.Reference_Count Between 60 And 69,"60-69",IIf(Q.Reference_Count Between 70 And 79,"70-79",IIf(Q.Reference_Count Between 80 And 89,"80-89",IIf(Q.Reference_Count Between 90 And 99,"90-99","100+"))))))))))) AS Ref_Bin
FROM Q_Article_Reference_Count AS Q;


--- Query: Q_Article_Reference_Count ---
SELECT A.ARTICLE_ID, FIRST(A.ARTICLE_TITLE) AS ARTICLE_TITLE, Count(R.REF_ID) AS Reference_Count
FROM ARTICLES AS A LEFT JOIN [REFERENCES] AS R ON A.ARTICLE_ID = R.ARTICLE_ID
GROUP BY A.ARTICLE_ID
ORDER BY A.ARTICLE_ID;


--- Query: Q_Article_Reference_Distribution ---
SELECT Ref_Bin, Count(*) AS Articles_In_Bin
FROM Q_Article_Ref_Bins
GROUP BY Ref_Bin
ORDER BY Ref_Bin;


--- Query: Q_Article_Subject ---
SELECT J.SUBJECT, Count(B.ARTICLE_ID) AS Articles, Sum(B.WebLocators) AS Total_WebLocators, Avg(B.WebLocators) AS Avg_WebLocators, Sum(B.DirectWebLinks) AS Total_DWL, Avg(B.DirectWebLinks) AS Avg_DWL, Sum(B.DOIRefs) AS Total_DOI, Avg(B.DOIRefs) AS Avg_DOI
FROM Q_Article_Level_Base AS B INNER JOIN JOURNALS AS J ON B.JOURNAL_ID = J.JOURNAL_ID
GROUP BY J.SUBJECT
ORDER BY J.SUBJECT;


--- Query: Q_Article_Year ---
SELECT B.P_YEAR, Count(B.ARTICLE_ID) AS Articles, Sum(B.WebLocators) AS Total_WebLocators, Avg(B.WebLocators) AS Avg_WebLocators, Sum(B.DirectWebLinks) AS Total_DWL, Avg(B.DirectWebLinks) AS Avg_DWL, Sum(B.DOIRefs) AS Total_DOI, Avg(B.DOIRefs) AS Avg_DOI
FROM Q_Article_Level_Base AS B
GROUP BY B.P_YEAR
ORDER BY B.P_YEAR;


--- Query: Q_ArticleRefCounts ---
SELECT A.ARTICLE_ID, A.P_YEAR, A.JOURNAL_ID, J.QUART, Count(R.REF_ID) AS RefCount
FROM (ARTICLES AS A INNER JOIN JOURNALS AS J ON A.JOURNAL_ID = J.JOURNAL_ID) LEFT JOIN [REFERENCES] AS R ON A.ARTICLE_ID = R.ARTICLE_ID
GROUP BY A.ARTICLE_ID, A.P_YEAR, A.JOURNAL_ID, J.QUART;


--- Query: Q_DOI_Resolver_URLs ---
SELECT R.REF_ID, R.ARTICLE_ID, Mid(
        R.REFERENCE_TEXT,
        IIf(
            InStr(1,R.REFERENCE_TEXT,"http://")>0,
            InStr(1,R.REFERENCE_TEXT,"http://"),
            IIf(
                InStr(1,R.REFERENCE_TEXT,"https://")>0,
                InStr(1,R.REFERENCE_TEXT,"https://"),
                InStr(1,R.REFERENCE_TEXT,"www.")
            )
        ),
        250
    ) AS DOI_URL
FROM [REFERENCES] AS R
WHERE (
        R.REFERENCE_TEXT Like "*http://*"
        OR R.REFERENCE_TEXT Like "*https://*"
        OR R.REFERENCE_TEXT Like "*www.*"
    )
    AND
    (
        R.REFERENCE_TEXT Like "*doi*org*"
    )
ORDER BY R.ARTICLE_ID, R.REF_ID;


--- Query: Q_DOIRefs_Per_Journal ---
SELECT A.JOURNAL_ID, Count(R.REF_ID) AS [DOI Refs]
FROM ARTICLES AS A INNER JOIN [REFERENCES] AS R ON A.ARTICLE_ID = R.ARTICLE_ID
WHERE (
        R.REFERENCE_TEXT Like "*doi*org*"
        OR R.REFERENCE_TEXT Like "*doi:*"
        OR R.REFERENCE_TEXT Like "*doi :*"
    )
GROUP BY A.JOURNAL_ID
ORDER BY A.JOURNAL_ID;


--- Query: Q_DOIRefs_Per_Quartile ---
SELECT J.QUART, Count(R.REF_ID) AS [DOI Refs]
FROM (JOURNALS AS J INNER JOIN ARTICLES AS A ON J.JOURNAL_ID = A.JOURNAL_ID) INNER JOIN [REFERENCES] AS R ON A.ARTICLE_ID = R.ARTICLE_ID
WHERE (
        R.REFERENCE_TEXT Like "*doi*org*"
        OR R.REFERENCE_TEXT Like "*doi:*"
        OR R.REFERENCE_TEXT Like "*doi :*"
    )
GROUP BY J.QUART
ORDER BY J.QUART;


--- Query: Q_DOIRefs_Per_Subject ---
SELECT J.SUBJECT, Count(R.REF_ID) AS [DOI Refs]
FROM (JOURNALS AS J INNER JOIN ARTICLES AS A ON J.JOURNAL_ID = A.JOURNAL_ID) INNER JOIN [REFERENCES] AS R ON A.ARTICLE_ID = R.ARTICLE_ID
WHERE (
        R.REFERENCE_TEXT Like "*doi*org*"
        OR R.REFERENCE_TEXT Like "*doi:*"
        OR R.REFERENCE_TEXT Like "*doi :*"
    )
GROUP BY J.SUBJECT
ORDER BY J.SUBJECT;


--- Query: Q_DOIRefs_Per_Year ---
SELECT A.P_YEAR, Count(R.REF_ID) AS [DOI Refs]
FROM ARTICLES AS A INNER JOIN [REFERENCES] AS R ON A.ARTICLE_ID = R.ARTICLE_ID
WHERE (
        R.REFERENCE_TEXT Like "*doi*org*"
        OR R.REFERENCE_TEXT Like "*doi:*"
        OR R.REFERENCE_TEXT Like "*doi :*"
    )
GROUP BY A.P_YEAR
ORDER BY A.P_YEAR;


--- Query: Q_Domain_Clean ---
SELECT T.REF_ID, T.ARTICLE_ID, T.DOMAIN, IIf(
        InStr(
            Replace(
                Replace(
                    Replace(
                        Replace(
                            Replace(LCase(Trim([DOMAIN])), "?", ""),
                        Chr(10), ""),
                    Chr(13), ""),
                "www.", ""),
            " ", ""),
        ":") > 0,
        Left(
            Replace(
                Replace(
                    Replace(
                        Replace(
                            Replace(LCase(Trim([DOMAIN])), "?", ""),
                        Chr(10), ""),
                    Chr(13), ""),
                "www.", ""),
            " ", ""),
            InStr(
                Replace(
                    Replace(
                        Replace(
                            Replace(
                                Replace(LCase(Trim([DOMAIN])), "?", ""),
                            Chr(10), ""),
                        Chr(13), ""),
                    "www.", ""),
                " ", ""),
            ":") - 1
        ),
        Replace(
            Replace(
                Replace(
                    Replace(
                        Replace(LCase(Trim([DOMAIN])), "?", ""),
                    Chr(10), ""),
                Chr(13), ""),
            "www.", ""),
        " ", "")
    ) AS DOMAIN_CLEAN
FROM tblURL_Domain_First AS T
WHERE T.DOMAIN Is Not Null
  AND T.DOMAIN <> "";


--- Query: Q_Domain_Clean_NoDOI ---
SELECT T.REF_ID, T.ARTICLE_ID, T.DOMAIN, LCase(Trim([DOMAIN])) AS DOMAIN_CLEAN
FROM Q_Domain_Without_DOIResolvers AS T
WHERE T.DOMAIN Is Not Null
  AND T.DOMAIN <> "";


--- Query: Q_Domain_Without_DOIResolvers ---
SELECT T.REF_ID, T.ARTICLE_ID, T.DOMAIN
FROM tblURL_Domain_First AS T LEFT JOIN Q_DOI_Resolver_URLs AS D ON T.REF_ID = D.REF_ID
WHERE D.REF_ID Is Null;


--- Query: Q_DWL_Per_Journal ---
SELECT A.JOURNAL_ID, Count(R.REF_ID) AS [Direct Web Links]
FROM ARTICLES AS A INNER JOIN [REFERENCES] AS R ON A.ARTICLE_ID = R.ARTICLE_ID
WHERE (
        R.REFERENCE_TEXT Like "*http://*"
        OR R.REFERENCE_TEXT Like "*https://*"
        OR R.REFERENCE_TEXT Like "*www.*"
    )
    AND NOT (
        R.REFERENCE_TEXT Like "*doi*org*"
    )
GROUP BY A.JOURNAL_ID
ORDER BY A.JOURNAL_ID;


--- Query: Q_DWL_Per_Quartile ---
SELECT J.QUART, Count(R.REF_ID) AS [Direct Web Links]
FROM (JOURNALS AS J INNER JOIN ARTICLES AS A ON J.JOURNAL_ID = A.JOURNAL_ID) INNER JOIN [REFERENCES] AS R ON A.ARTICLE_ID = R.ARTICLE_ID
WHERE (
        R.REFERENCE_TEXT Like "*http://*"
        OR R.REFERENCE_TEXT Like "*https://*"
        OR R.REFERENCE_TEXT Like "*www.*"
    )
    AND NOT (
        R.REFERENCE_TEXT Like "*doi*org*"
    )
GROUP BY J.QUART
ORDER BY J.QUART;


--- Query: Q_DWL_Per_Subject ---
SELECT J.SUBJECT, Count(R.REF_ID) AS [Direct Web Links]
FROM (JOURNALS AS J INNER JOIN ARTICLES AS A ON J.JOURNAL_ID = A.JOURNAL_ID) INNER JOIN [REFERENCES] AS R ON A.ARTICLE_ID = R.ARTICLE_ID
WHERE (
        R.REFERENCE_TEXT Like "*http://*"
        OR R.REFERENCE_TEXT Like "*https://*"
        OR R.REFERENCE_TEXT Like "*www.*"
    )
    AND NOT (
        R.REFERENCE_TEXT Like "*doi*org*"
    )
GROUP BY J.SUBJECT
ORDER BY J.SUBJECT;


--- Query: Q_DWL_Per_Year ---
SELECT A.P_YEAR, Count(R.REF_ID) AS [Direct Web Links]
FROM ARTICLES AS A INNER JOIN [REFERENCES] AS R ON A.ARTICLE_ID = R.ARTICLE_ID
WHERE (
        R.REFERENCE_TEXT Like "*http://*"
        OR R.REFERENCE_TEXT Like "*https://*"
        OR R.REFERENCE_TEXT Like "*www.*"
    )
    AND NOT (
        R.REFERENCE_TEXT Like "*doi*org*"
    )
GROUP BY A.P_YEAR
ORDER BY A.P_YEAR;


--- Query: Q_File_Extension_Extract ---
SELECT Extracted_URL, "pdf" AS File_Extension
FROM Q_URL_Lower
WHERE URL_Lower Like "*.pdf*"

UNION ALL
SELECT Extracted_URL, "docx" AS File_Extension
FROM Q_URL_Lower
WHERE URL_Lower Like "*.docx*"

UNION ALL
SELECT Extracted_URL, "doc" AS File_Extension
FROM Q_URL_Lower
WHERE URL_Lower Like "*.doc*"
  AND URL_Lower Not Like "*.docx*"

UNION ALL
SELECT Extracted_URL, "xlsx" AS File_Extension
FROM Q_URL_Lower
WHERE URL_Lower Like "*.xlsx*"

UNION ALL
SELECT Extracted_URL, "xls" AS File_Extension
FROM Q_URL_Lower
WHERE URL_Lower Like "*.xls*"
  AND URL_Lower Not Like "*.xlsx*"

UNION ALL
SELECT Extracted_URL, "pptx" AS File_Extension
FROM Q_URL_Lower
WHERE URL_Lower Like "*.pptx*"

UNION ALL
SELECT Extracted_URL, "ppt" AS File_Extension
FROM Q_URL_Lower
WHERE URL_Lower Like "*.ppt*"
  AND URL_Lower Not Like "*.pptx*"

UNION ALL
SELECT Extracted_URL, "txt" AS File_Extension
FROM Q_URL_Lower
WHERE URL_Lower Like "*.txt*"

UNION ALL
SELECT Extracted_URL, "html" AS File_Extension
FROM Q_URL_Lower
WHERE URL_Lower Like "*.html*"

UNION ALL
SELECT Extracted_URL, "htm" AS File_Extension
FROM Q_URL_Lower
WHERE URL_Lower Like "*.htm*"
  AND URL_Lower Not Like "*.html*"

UNION ALL
SELECT Extracted_URL, "php" AS File_Extension
FROM Q_URL_Lower
WHERE URL_Lower Like "*.php*"

UNION ALL
SELECT Extracted_URL, "aspx" AS File_Extension
FROM Q_URL_Lower
WHERE URL_Lower Like "*.aspx*"

UNION ALL
SELECT Extracted_URL, "asp" AS File_Extension
FROM Q_URL_Lower
WHERE URL_Lower Like "*.asp*"
  AND URL_Lower Not Like "*.aspx*"

UNION ALL
SELECT Extracted_URL, "jsp" AS File_Extension
FROM Q_URL_Lower
WHERE URL_Lower Like "*.jsp*"

UNION ALL
SELECT Extracted_URL, "cfm" AS File_Extension
FROM Q_URL_Lower
WHERE URL_Lower Like "*.cfm*"

UNION ALL
SELECT Extracted_URL, "csv" AS File_Extension
FROM Q_URL_Lower
WHERE URL_Lower Like "*.csv*"

UNION ALL
SELECT Extracted_URL, "json" AS File_Extension
FROM Q_URL_Lower
WHERE URL_Lower Like "*.json*"

UNION ALL
SELECT Extracted_URL, "xml" AS File_Extension
FROM Q_URL_Lower
WHERE URL_Lower Like "*.xml*"

UNION ALL SELECT Extracted_URL, "zip" AS File_Extension
FROM Q_URL_Lower
WHERE URL_Lower Like "*.zip*";


--- Query: Q_File_Extension_Flat ---
SELECT File_Extension
FROM Q_File_Extension_Extract
WHERE File_Extension Is Not Null;


--- Query: Q_Journal_WebLocator_Table ---
SELECT J.JOURNAL_ID, J.JOURNAL_NAME, Nz(T.[Total Refs],0) AS [Total Refs], Nz(W.[Web Locators],0) AS [Web Locators], Round(Nz(W.[Web Locators],0) / Nz(T.[Total Refs],1) * 100, 2) AS [% Web Locators], Nz(D.[Direct Web Links],0) AS [Direct Web Links], Round(Nz(D.[Direct Web Links],0) / Nz(T.[Total Refs],1) * 100, 2) AS [% Direct Web Links], Nz(O.[DOI Refs],0) AS [DOI Refs], Round(Nz(O.[DOI Refs],0) / Nz(T.[Total Refs],1) * 100, 2) AS [% DOI]
FROM (((JOURNALS AS J LEFT JOIN Q_TotalRefs_Per_Journal AS T ON J.JOURNAL_ID = T.JOURNAL_ID) LEFT JOIN Q_WebLocators_Per_Journal AS W ON J.JOURNAL_ID = W.JOURNAL_ID) LEFT JOIN Q_DWL_Per_Journal AS D ON J.JOURNAL_ID = D.JOURNAL_ID) LEFT JOIN Q_DOIRefs_Per_Journal AS O ON J.JOURNAL_ID = O.JOURNAL_ID
ORDER BY J.JOURNAL_ID;


--- Query: Q_Mixed_FirstURL ---
SELECT Q.REF_ID, Q.ARTICLE_ID, Q.REFERENCE_TEXT, IIf(Q.PosHttp>0,Q.PosHttp,IIf(Q.PosHttps>0,Q.PosHttps,Q.PosWww)) AS URL_Start
FROM Q_Mixed_URLStart AS Q
WHERE Q.PosHttp > 0     OR Q.PosHttps > 0     OR Q.PosWww > 0;


--- Query: Q_Mixed_FirstURL_v01 ---
SELECT R.REF_ID, R.ARTICLE_ID, R.REFERENCE_TEXT, Mid(
        R.REFERENCE_TEXT,
        IIf(
            InStr(R.REFERENCE_TEXT,"http://")>0,
            InStr(R.REFERENCE_TEXT,"http://"),
            IIf(
                InStr(R.REFERENCE_TEXT,"https://")>0,
                InStr(R.REFERENCE_TEXT,"https://"),
                InStr(R.REFERENCE_TEXT,"www.")
            )
        ),
        250
    ) AS FIRST_URL
FROM [REFERENCES] AS R
WHERE (
    R.REFERENCE_TEXT Like "*http://*"
    OR R.REFERENCE_TEXT Like "*https://*"
    OR R.REFERENCE_TEXT Like "*www.*"
)
AND
(
    R.REFERENCE_TEXT Like "*doi*org*"
    OR R.REFERENCE_TEXT Like "*doi:*"
    OR R.REFERENCE_TEXT Like "*doi :*"
);


--- Query: Q_Mixed_URLStart ---
SELECT R.REF_ID, R.ARTICLE_ID, R.REFERENCE_TEXT, InStr(1,R.REFERENCE_TEXT,"http://") AS PosHttp, InStr(1,R.REFERENCE_TEXT,"https://") AS PosHttps, InStr(1,R.REFERENCE_TEXT,"www.") AS PosWww
FROM [REFERENCES] AS R
WHERE (     R.REFERENCE_TEXT Like "*http://*"     OR R.REFERENCE_TEXT Like "*https://*"     OR R.REFERENCE_TEXT Like "*www.*" ) AND (     R.REFERENCE_TEXT Like "*doi*org*"     OR R.REFERENCE_TEXT Like "*doi:*"     OR R.REFERENCE_TEXT Like "*doi :*" );


--- Query: Q_Platform_Classification ---
SELECT Extracted_URL, "Commercial publisher" AS Platform_Type
FROM Total_Direct_Web_Links
WHERE Extracted_URL Like "*sciencedirect.com*" OR Extracted_URL Like "*elsevier.com*"

UNION ALL
SELECT Extracted_URL, "Commercial publisher"
FROM Total_Direct_Web_Links
WHERE Extracted_URL Like "*springer*" OR Extracted_URL Like "*springernature.com*"

UNION ALL
SELECT Extracted_URL, "Commercial publisher"
FROM Total_Direct_Web_Links
WHERE Extracted_URL Like "*wiley.com*"

UNION ALL
SELECT Extracted_URL, "Commercial publisher"
FROM Total_Direct_Web_Links
WHERE Extracted_URL Like "*tandfonline.com*" OR Extracted_URL Like "*taylorfrancis.com*"

UNION ALL
SELECT Extracted_URL, "Commercial publisher"
FROM Total_Direct_Web_Links
WHERE Extracted_URL Like "*sagepub.com*"

UNION ALL
SELECT Extracted_URL, "Commercial publisher"
FROM Total_Direct_Web_Links
WHERE Extracted_URL Like "*nature.com*"

UNION ALL
SELECT Extracted_URL, "Commercial publisher"
FROM Total_Direct_Web_Links
WHERE Extracted_URL Like "*mdpi.com*"


UNION ALL
SELECT Extracted_URL, "Repository / aggregator"
FROM Total_Direct_Web_Links
WHERE Extracted_URL Like "*jstor.org*"

UNION ALL
SELECT Extracted_URL, "Repository / aggregator"
FROM Total_Direct_Web_Links
WHERE Extracted_URL Like "*arxiv.org*"

UNION ALL
SELECT Extracted_URL, "Repository / aggregator"
FROM Total_Direct_Web_Links
WHERE Extracted_URL Like "*handle.net*"

UNION ALL
SELECT Extracted_URL, "Repository / aggregator"
FROM Total_Direct_Web_Links
WHERE Extracted_URL Like "*semanticscholar.org*"

UNION ALL
SELECT Extracted_URL, "Repository / aggregator"
FROM Total_Direct_Web_Links
WHERE Extracted_URL Like "*ssrn.com*"

UNION ALL
SELECT Extracted_URL, "Repository / aggregator"
FROM Total_Direct_Web_Links
WHERE Extracted_URL Like "*core.ac.uk*"

UNION ALL
SELECT Extracted_URL, "Repository / aggregator"
FROM Total_Direct_Web_Links
WHERE Extracted_URL Like "*zenodo.org*"

UNION ALL
SELECT Extracted_URL, "Repository / aggregator"
FROM Total_Direct_Web_Links
WHERE Extracted_URL Like "*archive.org*"

UNION ALL
SELECT Extracted_URL, "Repository / aggregator"
FROM Total_Direct_Web_Links
WHERE Extracted_URL Like "*proquest.com*"

UNION ALL
SELECT Extracted_URL, "Repository / aggregator"
FROM Total_Direct_Web_Links
WHERE Extracted_URL Like "*scopus.com*"


UNION ALL
SELECT Extracted_URL, "Academic society"
FROM Total_Direct_Web_Links
WHERE Extracted_URL Like "*ieee.org*" OR Extracted_URL Like "*acm.org*" OR Extracted_URL Like "*aps.org*"



UNION ALL
SELECT Extracted_URL, "Institutional"
FROM Total_Direct_Web_Links
WHERE Extracted_URL Like "*nih.gov*" OR Extracted_URL Like "*ncbi.nlm.nih.gov*" OR Extracted_URL Like "*unesco.org*" 
   OR Extracted_URL Like "*un.org*" OR Extracted_URL Like "*europa.eu*" OR Extracted_URL Like "*oecd.org*" 
   OR Extracted_URL Like "*who.int*" OR Extracted_URL Like "*worldbank.org*" OR Extracted_URL Like "*gov.uk*" 
   OR Extracted_URL Like "*ed.gov*" OR Extracted_URL Like "*nasa.gov*" OR Extracted_URL Like "*census.gov*"



UNION ALL
SELECT Extracted_URL, "Research platform"
FROM Total_Direct_Web_Links
WHERE Extracted_URL Like "*researchgate.net*" OR Extracted_URL Like "*academia.edu*" OR Extracted_URL Like "*github.com*"



UNION ALL
SELECT Extracted_URL, "Media"
FROM Total_Direct_Web_Links
WHERE Extracted_URL Like "*nytimes.com*" OR Extracted_URL Like "*theguardian.com*" 
   OR Extracted_URL Like "*reuters.com*" OR Extracted_URL Like "*bbc.com*" 
   OR Extracted_URL Like "*forbes.com*" OR Extracted_URL Like "*washingtonpost.com*" 
   OR Extracted_URL Like "*wsj.com*"



UNION ALL
SELECT Extracted_URL, "Platform"
FROM Total_Direct_Web_Links
WHERE Extracted_URL Like "*youtube.com*" OR Extracted_URL Like "*google.*" OR Extracted_URL Like "*facebook.com*"



UNION ALL SELECT Extracted_URL, "URL shortener"
FROM Total_Direct_Web_Links
WHERE Extracted_URL Like "*bit.ly*" OR Extracted_URL Like "*tinyurl.com*";


--- Query: Q_Platform_Classification_F ---
SELECT DOMAIN, "Commercial publisher" AS Platform_Type
FROM tblURL_Domain_First
WHERE DOMAIN Like "*sciencedirect.com*" OR DOMAIN Like "*elsevier.com*"

UNION ALL
SELECT DOMAIN, "Commercial publisher"
FROM tblURL_Domain_First
WHERE DOMAIN Like "*springer*" OR DOMAIN Like "*springernature.com*"

UNION ALL
SELECT DOMAIN, "Commercial publisher"
FROM tblURL_Domain_First
WHERE DOMAIN Like "*wiley.com*"

UNION ALL
SELECT DOMAIN, "Commercial publisher"
FROM tblURL_Domain_First
WHERE DOMAIN Like "*tandfonline.com*" OR DOMAIN Like "*taylorfrancis.com*"

UNION ALL
SELECT DOMAIN, "Commercial publisher"
FROM tblURL_Domain_First
WHERE DOMAIN Like "*sagepub.com*"

UNION ALL
SELECT DOMAIN, "Commercial publisher"
FROM tblURL_Domain_First
WHERE DOMAIN Like "*nature.com*"

UNION ALL
SELECT DOMAIN, "Commercial publisher"
FROM tblURL_Domain_First
WHERE DOMAIN Like "*mdpi.com*"


UNION ALL
SELECT DOMAIN, "Repository / aggregator"
FROM tblURL_Domain_First
WHERE DOMAIN Like "*jstor.org*"

UNION ALL
SELECT DOMAIN, "Repository / aggregator"
FROM tblURL_Domain_First
WHERE DOMAIN Like "*arxiv.org*"

UNION ALL
SELECT DOMAIN, "Repository / aggregator"
FROM tblURL_Domain_First
WHERE DOMAIN Like "*handle.net*"

UNION ALL
SELECT DOMAIN, "Repository / aggregator"
FROM tblURL_Domain_First
WHERE DOMAIN Like "*semanticscholar.org*"

UNION ALL
SELECT DOMAIN, "Repository / aggregator"
FROM tblURL_Domain_First
WHERE DOMAIN Like "*ssrn.com*"

UNION ALL
SELECT DOMAIN, "Repository / aggregator"
FROM tblURL_Domain_First
WHERE DOMAIN Like "*core.ac.uk*"

UNION ALL
SELECT DOMAIN, "Repository / aggregator"
FROM tblURL_Domain_First
WHERE DOMAIN Like "*zenodo.org*"

UNION ALL
SELECT DOMAIN, "Repository / aggregator"
FROM tblURL_Domain_First
WHERE DOMAIN Like "*archive.org*"

UNION ALL
SELECT DOMAIN, "Repository / aggregator"
FROM tblURL_Domain_First
WHERE DOMAIN Like "*proquest.com*"

UNION ALL
SELECT DOMAIN, "Repository / aggregator"
FROM tblURL_Domain_First
WHERE DOMAIN Like "*scopus.com*"


UNION ALL
SELECT DOMAIN, "Academic society"
FROM tblURL_Domain_First
WHERE DOMAIN Like "*ieee.org*" OR DOMAIN Like "*acm.org*" OR DOMAIN Like "*aps.org*"



UNION ALL
SELECT DOMAIN, "Institutional"
FROM tblURL_Domain_First
WHERE DOMAIN Like "*nih.gov*" OR DOMAIN Like "*ncbi.nlm.nih.gov*" OR DOMAIN Like "*unesco.org*" 
   OR DOMAIN Like "*un.org*" OR DOMAIN Like "*europa.eu*" OR DOMAIN Like "*oecd.org*" 
   OR DOMAIN Like "*who.int*" OR DOMAIN Like "*worldbank.org*" OR DOMAIN Like "*gov.uk*" 
   OR DOMAIN Like "*ed.gov*" OR DOMAIN Like "*nasa.gov*" OR DOMAIN Like "*census.gov*"



UNION ALL
SELECT DOMAIN, "Research platform"
FROM tblURL_Domain_First
WHERE DOMAIN Like "*researchgate.net*" OR DOMAIN Like "*academia.edu*" OR DOMAIN Like "*github.com*"



UNION ALL
SELECT DOMAIN, "Media"
FROM tblURL_Domain_First
WHERE DOMAIN Like "*nytimes.com*" OR DOMAIN Like "*theguardian.com*" 
   OR DOMAIN Like "*reuters.com*" OR DOMAIN Like "*bbc.com*" 
   OR DOMAIN Like "*forbes.com*" OR DOMAIN Like "*washingtonpost.com*" 
   OR DOMAIN Like "*wsj.com*"



UNION ALL
SELECT DOMAIN, "Platform"
FROM tblURL_Domain_First
WHERE DOMAIN Like "*youtube.com*" OR DOMAIN Like "*google.*" OR DOMAIN Like "*facebook.com*"



UNION ALL SELECT DOMAIN, "URL shortener"
FROM tblURL_Domain_First
WHERE DOMAIN Like "*bit.ly*" OR DOMAIN Like "*tinyurl.com*";


--- Query: Q_Platform_Count_F ---
SELECT Platform_Type, DOMAIN, Count(*) AS Domain_Count
FROM Q_Platform_Classification_F
GROUP BY Platform_Type, DOMAIN
ORDER BY Platform_Type, Count(*) DESC;


--- Query: Q_Platform_Group_Total ---
SELECT Platform_Type, Count(*) AS Platform_Count
FROM Q_Platform_Classification
GROUP BY Platform_Type
ORDER BY Count(*) DESC;


--- Query: Q_Platform_Group_Total_F ---
SELECT Platform_Type, Count(*) AS Platform_Count
FROM Q_Platform_Classification
GROUP BY Platform_Type
ORDER BY Count(*) DESC;


--- Query: Q_punctuation_cleaning ---
SELECT REF_ID, ARTICLE_ID, Replace(
    Replace(
        Replace(
            Replace(
                LCase(Trim([DOMAIN])),
                "(", ""
            ),
            ")", ""
        ),
        "?", ""
    ),
    ">", ""
) AS DOMAIN_CLEAN
FROM tblURL_Domain_First0;


--- Query: Q_Quartile_WebLocator_Table ---
SELECT T.QUART, Nz(T.[Total Refs],0) AS [Total Refs], Nz(W.[Web Locators],0) AS [Web Locators], Round(Nz(W.[Web Locators],0) / Nz(T.[Total Refs],1) * 100, 2) AS [% Web Locators], Nz(D.[Direct Web Links],0) AS [Direct Web Links], Round(Nz(D.[Direct Web Links],0) / Nz(T.[Total Refs],1) * 100, 2) AS [% Direct Web Links], Nz(O.[DOI Refs],0) AS [DOI Refs], Round(Nz(O.[DOI Refs],0) / Nz(T.[Total Refs],1) * 100, 2) AS [% DOI]
FROM ((Q_TotalRefs_Per_Quartile AS T LEFT JOIN Q_WebLocators_Per_Quartile AS W ON T.QUART = W.QUART) LEFT JOIN Q_DWL_Per_Quartile AS D ON T.QUART = D.QUART) LEFT JOIN Q_DOIRefs_Per_Quartile AS O ON T.QUART = O.QUART
ORDER BY T.QUART;


--- Query: Q_SLD_Count_Percentage ---
SELECT SLD, Count(*) AS SLD_Count, Round(
        100 * Count(*) /
        (SELECT Count(*) FROM Q_SLD_Extract_F
         WHERE SLD Is Not Null AND SLD <> ""),
    2) AS Percentage
FROM Q_SLD_Extract_F
WHERE SLD Is Not Null AND SLD <> ""
GROUP BY SLD
ORDER BY Count(*) DESC;


--- Query: Q_SLD_Extract ---
SELECT REF_ID, ARTICLE_ID, DOMAIN_CLEAN, IIf(InStrRev(DOMAIN_CLEAN,".")=0,Null,Mid(DOMAIN_CLEAN,InStrRev(DOMAIN_CLEAN,".",InStrRev(DOMAIN_CLEAN,".")-1)+1,InStrRev(DOMAIN_CLEAN,".")-InStrRev(DOMAIN_CLEAN,".",InStrRev(DOMAIN_CLEAN,".")-1)-1)) AS SLD
FROM Q_Domain_Clean_NoDOI;


--- Query: Q_SLD_Extract_Safe ---
SELECT REF_ID, ARTICLE_ID, DOMAIN, DOMAIN_CLEAN, IIf(DOMAIN_CLEAN Is Null Or DOMAIN_CLEAN="" Or InStr(1,DOMAIN_CLEAN,".")=0 Or InStrRev(DOMAIN_CLEAN,".",InStrRev(DOMAIN_CLEAN,".")-1)=0,"unresolved",Mid(DOMAIN_CLEAN,InStrRev(DOMAIN_CLEAN,".",InStrRev(DOMAIN_CLEAN,".")-1)+1,InStrRev(DOMAIN_CLEAN,".")-InStrRev(DOMAIN_CLEAN,".",InStrRev(DOMAIN_CLEAN,".")-1)-1)) AS SLD
FROM Q_Domain_Clean_NoDOI;


--- Query: Q_Subject_WebLocator_Table ---
SELECT T.SUBJECT, Nz(T.[Total Refs],0) AS [Total Refs], Nz(W.[Web Locators],0) AS [Web Locators], Round(Nz(W.[Web Locators],0)/Nz(T.[Total Refs],1)*100,2) AS [% Web Locators], Nz(D.[Direct Web Links],0) AS [Direct Web Links], Round(Nz(D.[Direct Web Links],0)/Nz(T.[Total Refs],1)*100,2) AS [% Direct Web Links], Nz(O.[DOI Refs],0) AS [DOI Refs], Round(Nz(O.[DOI Refs],0)/Nz(T.[Total Refs],1)*100,2) AS [% DOI]
FROM ((Q_TotalRefs_Per_Subject AS T LEFT JOIN Q_WebLocators_Per_Subject AS W ON T.SUBJECT = W.SUBJECT) LEFT JOIN Q_DWL_Per_Subject AS D ON T.SUBJECT = D.SUBJECT) LEFT JOIN Q_DOIRefs_Per_Subject AS O ON T.SUBJECT = O.SUBJECT
ORDER BY T.SUBJECT;


--- Query: Q_Titles_SM ---
SELECT A.JOURNAL_ID, Sum(IIf(LCase(A.ARTICLE_TITLE) Like "*facebook*" 
         OR LCase(A.ARTICLE_TITLE) Like "*fb.com*" 
         OR LCase(A.ARTICLE_TITLE) Like "*fb.me*",1,0)) AS Facebook, Sum(IIf(LCase(A.ARTICLE_TITLE) Like "*youtube*" 
         OR LCase(A.ARTICLE_TITLE) Like "*youtu.be*",1,0)) AS YouTube, Sum(IIf(LCase(A.ARTICLE_TITLE) Like "*twitter*" 
         OR LCase(A.ARTICLE_TITLE) Like "*x.com*" 
         OR LCase(A.ARTICLE_TITLE) Like "*tweet*",1,0)) AS Twitter_X, Sum(IIf(LCase(A.ARTICLE_TITLE) Like "*instagram*",1,0)) AS Instagram, Sum(IIf(LCase(A.ARTICLE_TITLE) Like "*linkedin*",1,0)) AS LinkedIn, Sum(IIf(LCase(A.ARTICLE_TITLE) Like "*reddit*",1,0)) AS Reddit, Sum(IIf(LCase(A.ARTICLE_TITLE) Like "*tiktok*",1,0)) AS TikTok, Sum(IIf(LCase(A.ARTICLE_TITLE) Like "*pinterest*",1,0)) AS Pinterest, Sum(IIf(LCase(A.ARTICLE_TITLE) Like "*snapchat*",1,0)) AS Snapchat, Sum(IIf(LCase(A.ARTICLE_TITLE) Like "*quora*",1,0)) AS Quora, Sum(IIf(LCase(A.ARTICLE_TITLE) Like "*threads*",1,0)) AS Threads, Sum(IIf(LCase(A.ARTICLE_TITLE) Like "*wechat*",1,0)) AS WeChat, Sum(IIf(LCase(A.ARTICLE_TITLE) Like "*weibo*",1,0)) AS Weibo, Sum(IIf(LCase(A.ARTICLE_TITLE) Like "*social media*",1,0)) AS Social_Media, Sum(IIf(LCase(A.ARTICLE_TITLE) Like "*social network*",1,0)) AS Social_Networks
FROM ARTICLES AS A
GROUP BY A.JOURNAL_ID
ORDER BY A.JOURNAL_ID;


--- Query: Q_TLD_Count ---
SELECT TLD, Count(*) AS TLD_Count
FROM Q_TLD_Extract_NoDOI
WHERE TLD Is Not Null
    AND TLD <> ""
GROUP BY TLD
ORDER BY Count(*) DESC;


--- Query: Q_TLD_Count_Percentage ---
SELECT TLD, Count(*) AS TLD_Count, Round(100 * Count(*) /
        (SELECT Count(*) FROM Q_TLD_Extract_NoDOI),2) AS Percentage
FROM Q_TLD_Extract_NoDOI
GROUP BY TLD
ORDER BY Count(*) DESC;


--- Query: Q_TLD_Counts ---
SELECT Q.TLD, Count(*) AS TLD_Count
FROM Q_TLD_Extract AS Q
GROUP BY Q.TLD
ORDER BY Count(*) DESC;


--- Query: Q_TLD_Extract ---
SELECT T.REF_ID, T.ARTICLE_ID, T.DOMAIN, LCase(Mid([DOMAIN],InStrRev([DOMAIN],".")+1)) AS TLD
FROM tblURL_Domain_First AS T
WHERE T.DOMAIN Is Not Null   AND T.DOMAIN <> "";


--- Query: Q_TLD_Extract_Clean ---
SELECT Q.REF_ID, Q.ARTICLE_ID, Q.DOMAIN, Q.DOMAIN_CLEAN, IIf(Q.DOMAIN_CLEAN Is Null Or Q.DOMAIN_CLEAN="" Or InStr(1,Q.DOMAIN_CLEAN,".")=0,Null,LCase(Mid(Q.DOMAIN_CLEAN,InStrRev(Q.DOMAIN_CLEAN,".")+1))) AS TLD
FROM Q_Domain_Clean AS Q;


--- Query: Q_TLD_Extract_NoDOI ---
SELECT REF_ID, ARTICLE_ID, DOMAIN, DOMAIN_CLEAN, IIf(DOMAIN_CLEAN Is Null Or DOMAIN_CLEAN="" Or InStr(1,DOMAIN_CLEAN,".")=0,"unresolved",LCase(Mid(DOMAIN_CLEAN,InStrRev(DOMAIN_CLEAN,".")+1))) AS TLD
FROM Q_Domain_Clean_NoDOI;


--- Query: Q_TotalRefs_Per_Journal ---
SELECT A.JOURNAL_ID, Count(R.REF_ID) AS [Total Refs]
FROM ARTICLES AS A INNER JOIN [REFERENCES] AS R ON A.ARTICLE_ID = R.ARTICLE_ID
GROUP BY A.JOURNAL_ID
ORDER BY A.JOURNAL_ID;


--- Query: Q_TotalRefs_Per_Quartile ---
SELECT J.QUART, Count(R.REF_ID) AS [Total Refs]
FROM (JOURNALS AS J INNER JOIN ARTICLES AS A ON J.JOURNAL_ID = A.JOURNAL_ID) INNER JOIN [REFERENCES] AS R ON A.ARTICLE_ID = R.ARTICLE_ID
GROUP BY J.QUART
ORDER BY J.QUART;


--- Query: Q_TotalRefs_Per_Subject ---
SELECT J.SUBJECT, Count(R.REF_ID) AS [Total Refs]
FROM (JOURNALS AS J INNER JOIN ARTICLES AS A ON J.JOURNAL_ID = A.JOURNAL_ID) INNER JOIN [REFERENCES] AS R ON A.ARTICLE_ID = R.ARTICLE_ID
GROUP BY J.SUBJECT
ORDER BY J.SUBJECT;


--- Query: Q_TotalRefs_Per_Year ---
SELECT A.P_YEAR, Count(R.REF_ID) AS [Total Refs]
FROM ARTICLES AS A INNER JOIN [REFERENCES] AS R ON A.ARTICLE_ID = R.ARTICLE_ID
GROUP BY A.P_YEAR
ORDER BY A.P_YEAR;


--- Query: Q_URL_Lower ---
SELECT Extracted_URL, LCase([Extracted_URL]) AS URL_Lower
FROM Total_Direct_Web_Links;


--- Query: Q_WebLocators_Per_Journal ---
SELECT A.JOURNAL_ID, Count(R.REF_ID) AS [Web Locators]
FROM ARTICLES AS A INNER JOIN [REFERENCES] AS R ON A.ARTICLE_ID = R.ARTICLE_ID
WHERE (
        R.REFERENCE_TEXT Like "*http://*"
        OR R.REFERENCE_TEXT Like "*https://*"
        OR R.REFERENCE_TEXT Like "*www.*"
        OR R.REFERENCE_TEXT Like "*doi*org*"
        OR R.REFERENCE_TEXT Like "*doi:*"
        OR R.REFERENCE_TEXT Like "*doi :*"
    )
GROUP BY A.JOURNAL_ID
ORDER BY A.JOURNAL_ID;


--- Query: Q_WebLocators_Per_Quartile ---
SELECT J.QUART, Count(R.REF_ID) AS [Web Locators]
FROM (JOURNALS AS J INNER JOIN ARTICLES AS A ON J.JOURNAL_ID = A.JOURNAL_ID) INNER JOIN [REFERENCES] AS R ON A.ARTICLE_ID = R.ARTICLE_ID
WHERE (
        R.REFERENCE_TEXT Like "*http://*"
        OR R.REFERENCE_TEXT Like "*https://*"
        OR R.REFERENCE_TEXT Like "*www.*"
        OR R.REFERENCE_TEXT Like "*doi*org*"
        OR R.REFERENCE_TEXT Like "*doi:*"
        OR R.REFERENCE_TEXT Like "*doi :*"
    )
GROUP BY J.QUART
ORDER BY J.QUART;


--- Query: Q_WebLocators_Per_Subject ---
SELECT J.SUBJECT, Count(R.REF_ID) AS [Web Locators]
FROM (JOURNALS AS J INNER JOIN ARTICLES AS A ON J.JOURNAL_ID = A.JOURNAL_ID) INNER JOIN [REFERENCES] AS R ON A.ARTICLE_ID = R.ARTICLE_ID
WHERE (
        R.REFERENCE_TEXT Like "*http://*"
        OR R.REFERENCE_TEXT Like "*https://*"
        OR R.REFERENCE_TEXT Like "*www.*"
        OR R.REFERENCE_TEXT Like "*doi*org*"
        OR R.REFERENCE_TEXT Like "*doi:*"
        OR R.REFERENCE_TEXT Like "*doi :*"
    )
GROUP BY J.SUBJECT
ORDER BY J.SUBJECT;


--- Query: Q_WebLocators_Per_Year ---
SELECT A.P_YEAR, Count(R.REF_ID) AS [Web Locators]
FROM ARTICLES AS A INNER JOIN [REFERENCES] AS R ON A.ARTICLE_ID = R.ARTICLE_ID
WHERE (
        R.REFERENCE_TEXT Like "*http://*"
        OR R.REFERENCE_TEXT Like "*https://*"
        OR R.REFERENCE_TEXT Like "*www.*"
        OR R.REFERENCE_TEXT Like "*doi*org*"
        OR R.REFERENCE_TEXT Like "*doi:*"
        OR R.REFERENCE_TEXT Like "*doi :*"
    )
GROUP BY A.P_YEAR
ORDER BY A.P_YEAR;


--- Query: Q_Year_WebLocator_Table ---
SELECT T.P_YEAR, Nz(T.[Total Refs],0) AS [Total Refs], Nz(W.[Web Locators],0) AS [Web Locators], Round(Nz(W.[Web Locators],0)/Nz(T.[Total Refs],1)*100,2) AS [% Web Locators], Nz(D.[Direct Web Links],0) AS [Direct Web Links], Round(Nz(D.[Direct Web Links],0)/Nz(T.[Total Refs],1)*100,2) AS [% Direct Web Links], Nz(O.[DOI Refs],0) AS [DOI Refs], Round(Nz(O.[DOI Refs],0)/Nz(T.[Total Refs],1)*100,2) AS [% DOI]
FROM ((Q_TotalRefs_Per_Year AS T LEFT JOIN Q_WebLocators_Per_Year AS W ON T.P_YEAR = W.P_YEAR) LEFT JOIN Q_DWL_Per_Year AS D ON T.P_YEAR = D.P_YEAR) LEFT JOIN Q_DOIRefs_Per_Year AS O ON T.P_YEAR = O.P_YEAR
ORDER BY T.P_YEAR;


--- Query: Q_YearQuart_RefsPerArticle ---
SELECT P_YEAR, QUART, Avg(RefCount) AS MeanRefsPerArticle
FROM Q_ArticleRefCounts
GROUP BY P_YEAR, QUART
ORDER BY P_YEAR, QUART;


--- Query: qryA_URL_Refs ---
SELECT R.REF_ID, R.ARTICLE_ID, Mid(R.REFERENCE_TEXT,IIf(InStr(1,R.REFERENCE_TEXT,"http://")>0,InStr(1,R.REFERENCE_TEXT,"http://"),IIf(InStr(1,R.REFERENCE_TEXT,"https://")>0,InStr(1,R.REFERENCE_TEXT,"https://"),InStr(1,R.REFERENCE_TEXT,"www."))),250) AS Extracted_URL
FROM [REFERENCES] AS R
WHERE (     R.REFERENCE_TEXT Like "*http://*"     OR R.REFERENCE_TEXT Like "*https://*"     OR R.REFERENCE_TEXT Like "*www.*" )
ORDER BY R.ARTICLE_ID, R.REF_ID;


--- Query: qryURL_AfterPrefix ---
SELECT Q.REF_ID, Q.ARTICLE_ID, Q.Extracted_URL, Q.URL_NoSpaces, IIf(Left(Q.URL_NoSpaces,8)="https://",Mid(Q.URL_NoSpaces,9),IIf(Left(Q.URL_NoSpaces,7)="http://",Mid(Q.URL_NoSpaces,8),IIf(Left(Q.URL_NoSpaces,4)="www.",Mid(Q.URL_NoSpaces,5),Q.URL_NoSpaces))) AS URL_AfterPrefix
FROM qryURL_Normalized AS Q;


--- Query: qryURL_Domain_First ---
SELECT Q.REF_ID, Q.ARTICLE_ID, Q.Extracted_URL, Q.URL_AfterPrefix, IIf(InStr(1,Q.URL_AfterPrefix,"/")>0,Left(Q.URL_AfterPrefix,InStr(1,Q.URL_AfterPrefix,"/")-1),Q.URL_AfterPrefix) AS [DOMAIN]
FROM qryURL_AfterPrefix AS Q
ORDER BY Q.ARTICLE_ID, Q.REF_ID;


--- Query: qryURL_Normalized ---
SELECT Q.REF_ID, Q.ARTICLE_ID, Q.Extracted_URL, LCase(Replace(Q.Extracted_URL," ","")) AS URL_NoSpaces
FROM qryA_URL_Refs AS Q;