<commit_before>CREATE OR REPLACE FUNCTION ndb.rawbymonth(startperiod integer default 0,
                                                endperiod integer default 1)
RETURNS TABLE (summary json)
AS
$function$
SELECT ds.datasetid,
        dsl.siteid,
        COUNT(DISTINCT(dsp.publicationid)) AS publications,
        COUNT(DISTINCT(pua.contactid)) AS authors,
        gpd.path[1] AS countrygpid,
        smp.observations
FROM ndb.datasets AS ds
JOIN ndb.datasetsubmissions AS dss on dss.datasetid = ds.datasetid
JOIN ndb.dslinks AS dsl ON dsl.datasetid = ds.datasetid
JOIN ndb.dsdampdata AS smp ON ds.datasetid = smp.datasetid
LEFT OUTER JOIN ndb.datasetpublications AS dsp ON dsp.datasetid = ds.datasetid
LEFT OUTER JOIN ndb.publicationauthors AS pua ON dsp.publicationid = pua.publicationid
JOIN ndb.sitegeopolitical AS sgp ON dsl.siteid = sgp.siteid
JOIN ndb.geopoldepth AS gpd ON gpd.geopoliticalid = sgp.geopoliticalid
WHERE
  EXTRACT(year from AGE(NOW(), dss.submissiondate))*12 +
   EXTRACT(month from AGE(NOW(), dss.submissiondate)) BETWEEN startperiod and endperiod
GROUP BY ds.datasetid, dsl.siteid, dsl.siteid, gpd.path[1], smp.observations
$function$ LANGUAGE SQL;<commit_msg>Modified rawbymonth function to add a CTE. Was accidentally overcounting things, needed to unnest the columns.<commit_after>CREATE OR REPLACE FUNCTION ndb.rawbymonth(startperiod integer default 0,
                                                endperiod integer default 1)
RETURNS TABLE (datasets bigint,
               sites bigint,
             publications, bigint,
           authors bigint,
         observations bigint)
AS
$function$
WITH rsum AS (
  SELECT ds.datasetid,
        dsl.siteid,
        array_agg(DISTINCT dsp.publicationid) AS publications,
        array_agg(DISTINCT pua.contactid) AS authors,
        gpd.path[1] AS countrygpid,
        smp.observations
  FROM ndb.datasets AS ds
  JOIN ndb.datasetsubmissions AS dss on dss.datasetid = ds.datasetid
  JOIN ndb.dslinks AS dsl ON dsl.datasetid = ds.datasetid
  JOIN ndb.dssampdata AS smp ON ds.datasetid = smp.datasetid
  LEFT OUTER JOIN ndb.datasetpublications AS dsp ON dsp.datasetid = ds.datasetid
  LEFT OUTER JOIN ndb.publicationauthors AS pua ON dsp.publicationid = pua.publicationid
  JOIN ndb.sitegeopolitical AS sgp ON dsl.siteid = sgp.siteid
  JOIN ndb.geopoldepth AS gpd ON gpd.geopoliticalid = sgp.geopoliticalid
  WHERE
    ds.datasetid < 10
  GROUP BY ds.datasetid, dsl.siteid, dsl.siteid, gpd.path[1], smp.observations)
SELECT COUNT(DISTINCT datasetid) AS datasets,
       COUNT(DISTINCT siteid) AS sites,
       COUNT(DISTINCT unpublications) AS publications,
       COUNT(DISTINCT unauthors) AS authors,
       COUNT(DISTINCT countrygpid) AS countries,
       SUM(observations) AS observations
FROM rsum,
						unnest(authors) AS unauthors,
						unnest(publications) AS unpublications
$function$ LANGUAGE SQL;
{'fraction_non_alphanumeric': 0.06895356895356895, 'fraction_numerical': 0.004158004158004158, 'mean_word_length': 3.354449472096531, 'pattern_counts': {'":': 0, '<': 4, '<?xml version=': 0, '>': 3, 'https://': 0, 'lorem ipsum': 0, 'www.': 0, 'xml': 0}, 'pii_count': 0, 'substrings_counts': 0, 'word_list_counts': {'cursed_substrings.json': 4, 'profanity_word_list.json': 0, 'sexual_word_list.json': 0, 'zh_pornsignals.json': 0}}
{'dir': 'git-commits-cleaned', 'id': '2731851', 'n_tokens_mistral': 1003, 'n_tokens_neox': 941, 'n_words': 271}