Find the most popular first name for women in 1885, 1915, 1945, 1975, and 2005 using SparkSQL

I need to find the most popular first name for women in 1885, 1915, 1945, 1975, and 2005.

TABLE INFO:

SSANames (firstName, year, gender)

EXAMPLE OF EXPECTED OUTPUT:

firstName / year / total_count (Martha, 1885, 732) (Bessy, 1915, 1004) (Charlotte, 1945, 999) (Ashley, 1975, 574) (Jessica, 2005, 942)

Having trouble figuring out how to create this query.

Here is where I am...

SELECT
  firstName,
  year,
  count(firstName) AS total_count
FROM SSANames
WHERE
  year IN ("1885", "1915", "1945", "1975", "2005")
GROUP BY
  firstName,
  year

I then need to run MAX on the total_count column for the given years, and return the associated firstName, but I'm unsure of how to do this.

Use window functions:

SELECT yf.*
FROM (SELECT year, firstName, COUNT(*) as cnt,
             ROW_NUMBER() OVER (PARTITION BY year ORDER BY COUNT(*) DESC) as seqnum
      FROM SSANames
      WHERE year IN (1885, 1915, 1945, 1975, 2005) AND
            gender = 'F'
      GROUP BY year, firstName
     ) yf
WHERE seqnum = 1;

Note that in the event of ties, this returns an arbitrary most common value. If you want all of them, use rank() instead of row_number().

An Ongoing PySpark Reference Guide: | by Austen Myers, spark.sql("select * from movielens where rating < 3").show(3) Say you wanted to find the most popular first names for each year with given totals of a the most popular first name for girls in 1885, 1915, 1945, 1975, and 2005. We can either create a new column that counts the number of females named� A list of the first names that have exactly 4 letters. 1880 1885 1890 1895 1900 1905 1910: 1915 1920 1925 1930 1935 1940 1945:

Hi! I think this is the answer you are looking for:

SELECT firstName, year, total
FROM (
      SELECT firstName, gender, year, total, 
      ROW_NUMBER() OVER (PARTITION BY YEAR ORDER BY TOTAL DESC) AS ROW_NUM
      FROM SSANames 
      WHERE gender = 'F' AND year IN (1885, 1915, 1945, 1975,2005) 
      GROUP BY firstName, gender, year, total ORDER BY total DESC
      ) SS
WHERE SS.ROW_NUM = 1

I hope this helps you.

Ray Bell, Spark can bring together various data files such as JDBC (SQL Server, called top10FemaleFirstNamesDF that contains the 10 most common female first names find the most popular first name for girls in 1885, 1915, 1945, 1975, and 2005:. Thank you for visiting the Newspaper Archive Collections! Did you know that newspapers are one of the most widely adopted sources of information for genealogists, students and researchers alike? Delve into the millions of newspaper articles organized by a vast assortment of categories, historical events and surnames from 1607 to present.

You can answer this question by using Spark SQL's Window Functions like this.

%sql
CREATE OR REPLACE TEMPORARY VIEW HistoricNames AS
SELECT
  firstName, year, total
FROM (
  SELECT
    firstName, year, total,
    dense_rank() OVER (PARTITION BY year ORDER BY total DESC) as rank
  FROM SSANames
  WHERE year IN (1885, 1915, 1945, 1975, 2005) AND gender = 'F'
  ) tmp
WHERE
  rank = 1
ORDER BY total

Read more about it here.

Ray Bell - Getting Started with Apache Spark SQL, The following SQL statement finds women born after 1990; it uses the year to find the most popular first name for girls in 1885, 1915, 1945, 1975, and 2005. (UPDATED Dec. 31, 2019. This continues to be the most popular and most frequently accessed of all Flashback Dallas posts. The online DMN archives — via the Dallas Public Library website — is frequently updated/redesigned. I try to update this page after each potentially confusing update.

I came up with this solution without using any window function but it does scan the SSANames table twice. Would be interested in knowing how to optimize this without using any window functions.

create table HistoricNames as
select lt.* from 
(
  select firstName, year, total 
  from SSANames
  where gender = 'F'
  and year in (1885, 1915, 1945, 1975, 2005)
) lt, 
( 
select year, max(total) as total from SSANames where gender = 'F' and year in (1885, 1915, 1945, 1975, 2005) group by year
) rt
where lt.year = rt.year and lt.total = rt.total;

Basic DataFrame Transformations in PySpark, Using PySpark to apply transformations to real datasets. we spend in PySpark, we'll likely be working with Spark DataFrames: this string of transformations to get a feel for what the middle portion of an First things first, we need to load this data into a DataFrame: You've done great, young Padawan. List of #1 Pop Singles for 1945. The list on this page is for all #1 hit Pop singles for 1945 using proprietary methods. The results in this chart are not affiliated with any mainstream or commercial chart and may not reflect charts seen elsewhere.

Timeline of United States inventions (before 1890), The United States provided many inventions in the time from the Colonial Period to the Gilded These days it is now known as an octant, the name given to it by its American Benjamin Franklin is credited with the invention of the first pair of bifocals in Babbitt is most commonly used as a thin surface layer in a complex, � List of #1 Pop Singles for 1911. The list on this page is for all #1 hit Pop singles for 1911 using proprietary methods. The results in this chart are not affiliated with any mainstream or commercial chart and may not reflect charts seen elsewhere.

[PDF] Racial Justice Timeline, 1860 1865 1870 1875 1880 1885 1890 1895 1900 1905 1910 1915 1920 1925 1945 1950 1955 1960 1965 1970 1975 1980 1985 1990 1995 2000 2005 white supremacy through intimidation and violence. 1899: Kim Seji becomes the first Bible Woman in Korea. The Great Migration: More than one million African. AbeBooks offers an immense collection of first edition books for sale, including some of the most famous books ever published. Find a first edition of your favorite book to add to your own collection or as a gift for the bibliophile in your life.

J. Walter Thompson Company (part 2), In 1930, JWT produced the first commercial television broadcast, a variety show As television became more popular in the 1950s, advertising to re-emphasize benefits of beer, a"Good for You" slogan that remained in use 1945-1960, and its name from The J. Walter Thompson Company to JWT in 2005 and issued an � Miriam "Ma" Ferguson was the first woman governor of Texas, serving two terms (1924-1926, and 1932-1934). She ran on a platform condemning the Ku Klux Klan, proposing spending cuts, and opposing Prohibition.

Comments
  • I don't see how your answer differs from the other answer. In fact, if I compare the two answers, it appears that yours contains an error. What is column total?
  • @Abra He's looking for a total, for example (Martha, 1885, 732), where 732 is the total. A few weeks ago I came across this exercise to develop, and the question was: Use the SSANames table to find the most popular first name for girls in 1885, 1915, 1945, 1975, and 2005. The table SSANames Has 4 columms (firstName,gender, year,total)
  • @Abra and the output has to be: firstName, year, total